Show previous result when no result in a cumulative sum in power bi

[qike74]

Hello,

Hope you could help me with the following. I'm trying to show a cumulative sum of HC per month.

HC results each month can be either +1, +2, -1, -2 etc... or nothing (no entry).

Looking at the screenshot, for example CDM, we have 5 HC in 2020-11 and there are no changes until 2021-3 where we have an increase of +1HC. I want to show the 5 HC throughout all the months in between these 2 dates. 

 

this is the meassure I'm using: 

Cumulative HC View =

CALCULATE(

    SUM(CPM[HC]),

    FILTER(ALL(CPM[Month]),CPM[Month]<=MAX(CPM[Month])))

any ideas?

thank you!

Enric

[v-jianboli-msft · Accepted solution]

Hi @qike74 ,

Please try:

First create a new table for Date:

Then manage relationship:

Apply the measure:

Cumulative HC View = 
CALCULATE(SUM(CPM[HC]),FILTER(ALL('DIM DATE'[Month]),[Month]<=MAX('DIM DATE'[Month])))

Final output:

Best Regards,

Jianbo Li

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

[qike74]

Thanks Jianbo. The blank month doesn't appear in the data because there is no particular action happening. It can also happen that there is a particular action happening in a month (p.e. +1 HC in CERTA, but no action in CDM) in that case I will see CDM showing blank instead of previous result and CERTA correctly showing the cumulated result. I would like to show always the cumulative at any case.

The data is sensitive and I find it hard to remove sensitive data without being valuable to understand the issue. This is a workforce file where we are showing the HC (head counts) per team retrospectively and forecast according to each action that happened or is expected (someone leave '-1, new hiring '+1, etc). 

[v-jianboli-msft]

Hi @qike74 ,

What does your raw data look like?

If the blank month have any data(such as blank values) in your raw data or just not appear?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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