Forum Discussion

ziyabikram96's avatar
ziyabikram96
Helper V
6 years ago

Running Total

Hi, I need to calculate running totals over hierarchical period i.e quarters,months,weeks. I have tried using STARTOFYEAR() and FIRSTDATE() but couldn't get the solution. I need to put it into the l...
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, ziyabikram96 

 

Running total calculation depends on the period used to display the result. I created data to reproduce your scenario to calculate the running total 3 month. The pbix file is attached in the end.

Table:

 

Calendar(a calculated table):

Calendar = CALENDARAUTO()

 

There is a relationship between two tables. You may create a measure as below.

Running total 3 month = 
IF(
    NOT(ISBLANK(SELECTEDVALUE('Table'[Date]))),
    CALCULATE(
        SUM('Table'[Value]),
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            -3,MONTH
        )
    )
)

 

Result:

 

Best Regards

Allan

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ziyabikram96's avatar
    ziyabikram96
    Helper V
    6 years ago

    But, it has to be dynamic. Like, I don't have to specifically mention "-3" using DATESINPERIOD function. It should take just first and last date and then calculate running total between that period.