Power BI: How to show previous result when no result found in a cumulative sum

Question

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

Answer 1

1. Create Calendar table:

Calendar = 
FILTER(
CALENDAR(FIRSTDATE('CPM'[Month]),LASTDATE('CPM'[Month])),
DAY([Date])=1)

2. Calculate [HC measure] and [Index column] in CPM table:

HC Measure = SUM(CPM[HC])

Index Column= [Month]&[Geo]

3. Create new table:

Table = ADDCOLUMNS(
UNION( ADDCOLUMNS('Calendar',"Geo","CDM"),
    ADDCOLUMNS('Calendar',"Geo","CERTA"),
    ADDCOLUMNS('Calendar',"Geo","Claims"),
    ADDCOLUMNS('Calendar',"Geo","Control Tower")),
"Index",[Date]&[Geo])

4. Set relationship in Model view:

5. Calculate columns in new table:

HC = CALCULATE([HC Measure])

Cumulative HC View = 
SUMX(FILTER(ALL('Table'),
    [Date]<=EARLIER('Table'[Date])&&
    [Geo]=EARLIER('Table'[Geo])),
    'Table'[HC])

6. Create table visual with columns in new table: