Running Total with Blank Month Values

[Anonymous]

I'm trying to create a calculation that shows the cumulative totals including for the ones with no data. I have been checking similar questions in this forum but nothing has solved my issue yet. I'm adding an example table below. In this example, the cumulative total column for the country BLG should be filled with the totals from November 2017 to today adding 2000 for each month (4000 for 2017 then 24000 for 2018,2019,2020 then for January 2021, 2000 and so on). Same for the country GER in which the calculation starts from July 2019. As for the last country, since there is no recurring contribution, the cumulative total is 5000. Any ideas?

Adding below one of the different methods I tried without success:

I created a new calendar table with a relationship to my main table.

CALENDAR = CALENDAR(DATE(2017,01,01),DATE(2025,12,31)

Created a measure to get annual total recurring contribution (which is probably where I'm mistaken)

ANNUAL = SUM(TABLE'[Recurring Contribution])

and the running total measure

Running Total= CALCULATE([ANNUAL], DATESYTD('Calendar'[Date], "31/12"))

Unfortunately, not only this measure resets every year, it also fills the months with no value prior to my inital date: Nov/17.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I will choose to create a calculated table. First, I need to create a measure to calculate the value of blank date.

Measure = 
var contribution =  
    CALCULATE(
        SUMX(
            FILTER('Table',
                'Table'[Start Date]<=MAX('Table 2'[Date]) 
                && (ISBLANK('Table'[End Date]) || 'Table'[End Date]>MAX('Table 2'[Date]))),
            ('Table'[Recurring Contribution])),
            CROSSFILTER('Table'[Start Date],'Table 2'[Date],None))
return IF(contribution=0,SUM('Table'[One-time Contribution]),contribution)

Then create a  calculated table.

Table 3 = 
var combination = CROSSJOIN('Table 2',VALUES('Table'[Country]))
return SUMMARIZE(combination,
    'Table 2'[Date].[Year],'Table 2'[Date].[Month],'Table'[Country],
    "value",[Measure])

You can refer to the sample .pbix

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

[amitchandak]

@Anonymous , You can create measure like

Cumm = CALCULATE([ANNUAL],filter(allselected(Table),Table[start Date] <=max(Table[Start Date])))

Cumm Sales = CALCULATE([ANNUAL],filter(allselected(date),date[date] <=max(date[date])))