Power BI

Acts as a Business Intelligence Architect and Senior expert in data modeling with Power BI. Your goal is to design an advanced DAX calculation solution to obtain the running total (Running Total) segmented by [Product/Service Category], ensuring that the accumulation logic respects the temporal hierarchy and does not overflow beyond the last date with actual transaction data in the [Facts Table Name] table.


The main challenge lies in precisely manipulating the filter context using the Power BI analytics engine. I need a formula that uses high performance functions like CALCULATE, FILTER, ALLSELECTED and MAX to correctly iterate over the fact table. The measure must be able to sum the [Metric to Sum] column from the start of the accounting period to the maximum date visible in the current filter context, ensuring that the calculation is strictly segregated by the values ​​present in the [Category Column] dimension.


Additionally, the formula must include validation logic to prevent visuals from showing accumulated values ​​in future periods where no actual transactions have yet occurred. This should be achieved using a logical condition that compares the MAX of the [Calendar Table Name] table with the MAX of the transactions date available in the model. It is critical that the measure be efficient for large-scale data models, minimizing row scanning by optimally using variables (VARs) to store intermediate states of the calculation.


Finally, it provides a technical explanation of how the ALLEXCEPT or KEEPFILTERS function could modify this behavior if the rollup is required to ignore certain external filters but maintain parent category targeting. The final result should include the formatted DAX code block, with extensive internal comments explaining the transition from row context to filter context and the importance of data lineage for the accuracy of the final report.

If any key information needed to fill the bracketed fields is missing, ask me the necessary questions before answering.

Act as a Senior Business Intelligence Consultant and Corporate Finance Expert to design an advanced analytical model in Power BI focused on a [Dynamic Income Statement]. Your objective is to create a data architecture that allows managers to view the financial performance of the company [Company_Name] interactively, allowing drill-down from the large accounting groups to the specific items by [Cost_Center] and [Business_Unit]. The model should be able to process multiple currencies if the user selects [Target_Currency] and compare actual performance against [Allocated_Budget].


The logical structure must be based on a report design table (Template Table) that defines the order of the rows (Sales, Costs, Gross Profit, Operating Expenses, EBITDA, etc.). You must generate the DAX code necessary for a master measure called [Dynamic_PL_Measure]. This measure should use functions such as SWITCH, SELECTEDVALUE, and CALCULATE to determine if the current row is a direct value from the [Accounting_Facts] table or if it is a calculated subtotal. Be sure to include logic to handle the sign of accounts (credits and debits) so that expenses are correctly subtracted from income without visual errors for the end user.


The prompt must include the creation of variance indicators (Variance Analysis). I need you to calculate the absolute and percentage difference between 'Current' and 'Target' for the period [Date_Range]. In addition, it integrates a time intelligence logic to compare the performance of the current quarter against [Previous_Quarter] and the same period of the previous year (YoY). The solution should allow dynamic filtering by [Product_Category] and [Geographic_Region], recalculating all contribution margins instantly.


Finally, describe the optimal visual configuration for this report. Suggests the use of a conditionally formatted matrix to highlight deviations that exceed [Percent_Tolerance]% and a Waterfall Chart to explain useful bridges. The proposal must be scalable for large volumes of data and allow the user to customize the display hierarchy using Field Parameters to switch between viewing the P&L by department or by product line.

If any key information needed to fill the bracketed fields is missing, ask me the necessary questions before answering.