Cumulative sum for all month until today even if a value with a month does not exist in the dataset

[anom]

Hi,

I would like to show cumulative sum for all month until current month in a table visual or a line chart but the data might have some months missing and I would still like to show them in the visuals.

For example, a sample data could be like below,

Here you can see there are no data for April and no data after June, but in PowerBI, I would still like to show cumulatives for those months, something like below,

Thank you.

[parry2k]

@anom As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

Now add the following measure  for cumulative total until the last transaction date (you can tweak it whatever you want) to make it work:

RT Sales = 
VAR MaxSalesDate = CALCULATE ( MAX ( 'Calendar'[Date] ), Sales )
VAR RT =
CALCULATE ( 
    [Sales],
    FILTER ( 
        ALLSELECTED ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) &&
        'Calendar'[Date] <= MaxSalesDate
    )
)
RETURN RT

[Ashish_Mathur]

Hi,

Ensure that you have a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

Total = sum(Data[Sales])

Total YTD = calculate([Total],datesytd(calendar[date],"31/12"))

Hope this helps.

[anom]

Hi, ytd resets running total for a new year, need the running total to continue as data will have many years. Thank you

[Ashish_Mathur]

Revise the YTD measure to

Total YTD = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

[anom]

Hi, this is showing running total for every single day/month for the date table. Is it possible to show for a specific date range?? (I will need to have a larger date range for date table for real use case and can not show the running total for every single one)

For example, I want to show only upto current month. There might be more lines with different date ranges but would like this line to be just until today. Thank you.

[Ashish_Mathur]

Expand the filter pane and apply a date filter.

[parry2k]

@anom do you have a date dimension in your model? What is your running total measure look like?

[anom]

cumulative_amount =

CALCULATE(

    [sum_amount],

    FILTER(

        ALLSELECTED(Data),

        Data[Date] <= MAX(Data[Date])

))