Forum Discussion

Gaurav_Lakhotia's avatar
Gaurav_Lakhotia
Helper III
5 years ago
Solved

Calculate Running Total

Hello,

 

I want to calculate the running total based on a measure.

 

Here is my measure,

AmountE &2021 - Actuals =
IF (
SUM ( 'PO_Data'[2021 - Actuals] ) = BLANK (),
SUM ( '2021E'[Amount2021E] ) + SUM ( 'PO_Data'[2021 - Actuals] ),
SUM ( 'PO_Data'[2021 - Actuals] )
)
 
Here is running total quick measure,
2021 - FY Estimate =
CALCULATE([AmountE &2021 - Actuals ],
    FILTER(
        CALCULATETABLE(
            SUMMARIZE(
                'MonthMapping',
                'MonthMapping'[Month Priority],
                'MonthMapping'[Month]
            ),
            ALLSELECTED('MonthMapping')
        ),
        ISONORAFTER(
            'MonthMapping'[Month Priority], MAX('MonthMapping'[Month Priority]), DESC,
            'MonthMapping'[Month], MAX('MonthMapping'[Month]), DESC
        )
    )
)
 
Output:

As you can see it is repeating the same value after July(till we have actuals). I want to calculate the running total based on the "AmountE &2021-Actual" measure(highlighted in green).

 

Thanks

 

 

Need Help
Show and Tell
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    5 years ago

    Gaurav_Lakhotia 

    Change your 1st Measure as follows and try:


    AmountE &2021 - Actuals  = 

SUMX(
    ALLSELECTED('MonthMapping'[Month],'MonthMapping'[Month Priority]),
    var __act = CALCULATE(SUM ( 'PO_Data'[2021 - Actuals] ) )
    var __2021e =  CALCULATE( SUM ( '2021E'[Amount2021E] ) )
    return
    IF( __act = BLANK (),  __2021e + __act , __act )
)

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Gaurav_Lakhotia 

    Change your 1st Measure as follows and try:


    AmountE &2021 - Actuals  = 

SUMX(
    ALLSELECTED('MonthMapping'[Month],'MonthMapping'[Month Priority]),
    var __act = CALCULATE(SUM ( 'PO_Data'[2021 - Actuals] ) )
    var __2021e =  CALCULATE( SUM ( '2021E'[Amount2021E] ) )
    return
    IF( __act = BLANK (),  __2021e + __act , __act )
)