cumulative sum of the past months

[knut12212]

Current Measure:

Running total in Monate =

CALCULATE(

    SUM('Salesdata'[Sales]),

    FILTER(

        ALLSELECTED('Salesdata'[Months]),

        ISONORAFTER('Salesdata'[Months], MAX('Salesdata'[Months]), DESC)

    )

)

How can I extend the formula so that only the cumulative sum of the past months is displayed? In the column Number of Past Periods the maximum value indicates how many periods have already passed. So if the value in the column Months is greater than the maximum value in the column Number of Past Periods, then the running total for that month should return 0.

I tried to do it with ChatGPT, but I couldn’t come up with a reliable solution, which is why I would appreciate any tips or a suggested solution.

[v-saisrao-msft]

Hi @knut12212,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @knut12212,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[knut12212]

I could not find a suitable solution. However, as the dashboard was due to last Friday, I did not change that

[v-saisrao-msft · Accepted solution]

Hi @knut12212,

Could you please describe the issue you're experiencing in detail? Also, kindly share the sample PBIX file along with the expected output so we can help resolve the problem.

Thank you.

[govind_021]

Hi @knut12212 
Please Try This

Running Total Past Months =
VAR MaxPastPeriods =
MAX ( 'Salesdata'[Number of Past Periods] )
VAR CurrentMonth =
MAX ( 'Salesdata'[Months] )
RETURN
IF (
CurrentMonth <= MaxPastPeriods,
CALCULATE (
SUM ( 'Salesdata'[Sales] ),
FILTER (
ALLSELECTED ( 'Salesdata'[Months] ),
ISONORAFTER ( 'Salesdata'[Months], CurrentMonth, DESC )
)
),
0
)

Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst

[v-saisrao-msft]

Hi @knut12212,

I reproduced your issue with sample data and was able to create the issue in Power BI. I've attached the PBIX file for your reference.

Running Total with Past Periods Limit = 
VAR CurrentMonth = MAX('Salesdata'[Months])
VAR MaxPastPeriods = MAX('Salesdata'[Number of Past Periods])
RETURN
CALCULATE(
    SUM('Salesdata'[Sales]),
    FILTER(
        ALLSELECTED('Salesdata'),
        'Salesdata'[Months] <= CurrentMonth &&
        'Salesdata'[Months] >= CurrentMonth - MaxPastPeriods + 1
    )
)

Hope this helps

Thank you.