Case study · Analytics & BI · Finance
Moving rolling calculations from Power BI to SQL
Refactoring and optimizing 3-, 6- and 12-month rolling calculations by moving complex DAX measures to the SQL layer. The solution covered the Actuals, Radar and Budget scenarios, kept the dynamic period selection in Power BI, simplified the measures and made business rules easier to maintain.
The report had a dynamic selector to switch between 3, 6 and 12 months, which led to many complex DAX measures with conditional calculations, filter-context manipulation and time logic, making maintenance harder and increasing the processing load in Power BI.
- Power BI
- DAX
- SQL
- Data Modeling
Solution
The approach was to move the rolling-calculation logic from Power BI to the SQL layer, keeping in the report only the measures needed to consume and present the results. The work had three main fronts:
Restructuring the SQL layer
Implementing the accumulation rules for 3, 6 and 12 months across the Actuals, Radar and Budget scenarios. The logic is now pre-computed in the data layer.
Keeping the dynamic analysis
The period selector was kept, but now as a filter on a native table column. Choosing 3, 6 or 12 months still determines which rolling total is shown.
Simplifying the DAX measures
With rolling values computed in SQL, the Power BI measures no longer hold all the time logic and simply consume results prepared at the source.
Technical challenges
The refactoring had to preserve the meaning of the calculations, not just reproduce sums in SQL.
Time windows
Making sure the 3-, 6- and 12-month totals respected the reference periods.
Multiple scenarios
Keeping Actuals, Radar and Budget consistent without mixing their rules.
Business context
Preserving granularity and the relevant filters, such as period and profit center.
Consistency across layers
Validating the values produced by SQL against the results of the original DAX measures.