Forum Discussion
Remaining Balance Depends on Prior Month
Hi everyone,
I’m building a project revenue recognition system in Power BI and I want to confirm whether this type of rolling calculation is possible in DAX, given DirectQuery constraints.
I’ll explain using a simplified monthly example.
Each project has:
• Contract Value
• Total Project Days
• Project Days per Month
• Optional User Override (cumulative, entered at month end)
Goal: calculate per month:
Recognized Revenue
Remaining Contract Value
At the begining : Remaining = Contract Value
For each month:
If override exists (override is cumulative-to-date):
Recognized =
Override − SUM(previous months recognized)
Remaining =
Contract − Override
If no override (straight-line on remaining):
Recognized =
Remaining × (Days in Month / Remaining Project Days)
Remaining =
Remaining − Recognized
So:
Remaining(month N) depends on Recognized(month N−1)
Recognized(month N) depends on Remaining(month N−1)
This creates a rolling dependency chain.
Example. Project duration from 1st of Jan 2026 = 31st of Dec 2026
Month Remaining Start Days Remaining Days Override Recognized
| Jan | 435,806 | 31 | 365 | 37,013 | |
| Feb | 398,793 | 28 | 334 | 33,432 | |
| Mar | 365,361 | 31 | 306 | 100,000 | 29,555 |
| Apr | 335,806 | 30 | 275 | 36,633 | |
| May | 299,173 | 31 | 245 | 37,855 | |
| … | … | … | … | … |
Override is cumulative:
March override = 100,000 means:
March Recognized =
100,000 − (Jan + Feb)
Remaining becomes:
Contract − 100,000
Then straight-line continues on the new remaining.
Technical Constraints
• Overrides must be live (using Qirect Query)
• Cannot create calculated tables
• Must be done in measures
But:
Recognized(month N) depends on Remaining(month N−1)
Remaining(month N) depends on Recognized(month N)
Question
Is this possible in pure DAX (with DirectQuery involved), or does this fundamentally require pre-materialization in SQL / Power Query?
Hello Alex_DLV,
No this cannot be implemented in pure DAX measures, including in DirectQuery or Microsoft Fabric semantic models.
Your calculation is recursive and stateful:- Remaining(N) depends on Recognized(N−1)
- Recognized(N) depends on Remaining(N−1)
- Overrides reset the cumulative state mid-timeline
DAX does not support recursive calculations or circular dependencies between measures. Measures are evaluated independently and do not carry forward the result of a prior period’s calculation. This is consistent with Microsoft’s description of DAX evaluation context and measure behavior.
Learn DAX basics in Power BI DesktopMicrosoft Fabric does support this scenario but not inside DAX.
Fabric’s architecture expects complex, stateful, or iterative transformations to be completed before data reaches the semantic model.
The semantic model (Direct Lake or DirectQuery) is designed for querying and aggregation, not procedural or recursive logic.
Direct Lake overviewConclusion:
The rolling recognized/remaining logic must be pre-materialized upstream (SQL Analytics Endpoint, Warehouse, Lakehouse, Dataflow, or Spark). DAX should then be used only for slicing, filtering, and aggregation over the precomputed results.Hi Alex_DLV,
DAX is not recursive and will never be.
Microsoft, though, provides a recursive language in Power BI for the ETL part: M (language of Power Query)
So my suggestion is:
1 - try to get the nrs precalculated from IT
2 - if (1) does not work or is not applicable, use M to perform these values
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
5 Replies
- FBergamaschi
Super User
Hi Alex_DLV,
DAX is not recursive and will never be.
Microsoft, though, provides a recursive language in Power BI for the ETL part: M (language of Power Query)
So my suggestion is:
1 - try to get the nrs precalculated from IT
2 - if (1) does not work or is not applicable, use M to perform these values
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
- GeraldGEmerick
Memorable Member
Alex_DLV On the surface, this looks like it is essentially recursive but some problems like this can be solved without recursion. However, I am not certain there is enough information here to determine if this is one of those cases that can be calculated without recursion. If it is a truly recursive issue, DAX is not going to be an option.
v-dineshyaCommunity Support
Hi Alex_DLV ,
Thank you for reaching out to the Microsoft Community Forum.
Hi GeraldGEmerick , Olufemi7 and FBergamaschi , Thank you for your prompt responses.
Hi Alex_DLV , could you please try the proposed solutions shared by GeraldGEmerick , Olufemi7 and FBergamaschi ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
- v-dineshya
Hi Alex_DLV ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
- Olufemi7
Hello Alex_DLV,
No this cannot be implemented in pure DAX measures, including in DirectQuery or Microsoft Fabric semantic models.
Your calculation is recursive and stateful:- Remaining(N) depends on Recognized(N−1)
- Recognized(N) depends on Remaining(N−1)
- Overrides reset the cumulative state mid-timeline
DAX does not support recursive calculations or circular dependencies between measures. Measures are evaluated independently and do not carry forward the result of a prior period’s calculation. This is consistent with Microsoft’s description of DAX evaluation context and measure behavior.
Learn DAX basics in Power BI DesktopMicrosoft Fabric does support this scenario but not inside DAX.
Fabric’s architecture expects complex, stateful, or iterative transformations to be completed before data reaches the semantic model.
The semantic model (Direct Lake or DirectQuery) is designed for querying and aggregation, not procedural or recursive logic.
Direct Lake overviewConclusion:
The rolling recognized/remaining logic must be pre-materialized upstream (SQL Analytics Endpoint, Warehouse, Lakehouse, Dataflow, or Spark). DAX should then be used only for slicing, filtering, and aggregation over the precomputed results.
