Remaining Balance Depends on Prior Month

[Alex_DLV]

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:

1. Recognized Revenue

2. 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?

[Olufemi7 · Accepted solution]

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 Desktop 

Microsoft 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 overview 

Conclusion:
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.

[v-dineshya]

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

[FBergamaschi · Accepted solution]

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

[Olufemi7 · Accepted solution]

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 Desktop 

Microsoft 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 overview 

Conclusion:
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.

[GeraldGEmerick]

@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.