cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Reverse Running Total for Forecast with condition - Dax Help

Hello friends,
can you help me to get a dax formula to get reverse running total of forecast

see my sample data
Gap = Planned - Actuals

how reverse running total is calculated is

if acutals for current month is present
then it took Reverse Running Total planned - Actuals + Gap, Here 27-1+1 = 27

if Actuals is not present the prev month RRT forecast- current month planned
example in jun no actuals the may forecast value - june planned value , 27 - 3 = 24

 Month Planned Reverse Running Total planned Actuals Gap Reverse Running Total Forecast May-24 2 27 1 1 27 Jun-24 3 25 24 Jul-24 4 22 20 Aug-24 5 18 15 Sep-24 6 13 9 Oct-24 7 7 2 Total 27
1 ACCEPTED SOLUTION
Super User

@RENJITH_R_S Try:

``````Column =
VAR __Month = [Month]
VAR __Actuals = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] <> BLANK() ), [Actuals] )
VAR __Gap = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] <> BLANK() ), [Gap] )
VAR __Planned = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] = BLANK() ), [Planned] )
VAR __MinMonth = MINX( 'Table', [Month])
VAR __Start = SUMX( FILTER( 'Table', [Month] = __MinMonth ), [Reverse Running Total planned] )
VAR __Result = __Start - __Actuals + __Gap - __Planned
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@RENJITH_R_S Try:

``````Column =
VAR __Month = [Month]
VAR __Actuals = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] <> BLANK() ), [Actuals] )
VAR __Gap = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] <> BLANK() ), [Gap] )
VAR __Planned = SUMX( FILTER( 'Table', [Month] <= __Month && [Actuals] = BLANK() ), [Planned] )
VAR __MinMonth = MINX( 'Table', [Month])
VAR __Start = SUMX( FILTER( 'Table', [Month] = __MinMonth ), [Reverse Running Total planned] )
VAR __Result = __Start - __Actuals + __Gap - __Planned
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Resolver I

Thanks @Greg_Deckler

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.