Creating Cumulative Line Chart

[phth017]

I have one data table (named Table) with columns Item, Quantity and Date. I changed the Date column format to "MMMM yyyy" as I want to get the cumulative sum over 12 months in year 2020. Therefore, I created a measure named Cumulative with the formula 

Cumulative = CALCULATE(SUM('Table'[Quantity]),filter(ALLSELECTED('Table'),'Table'[Date]<=max('Table'[Date])))

The line chart I got from the above formula is as below:

As you can see, the cumulative line chart does not display the month September & December because I do not have any data for these two months.

Next, I tried with another method to create a new table (named Table5) with a text column Mth/Yr (12 months) with format "MMMM yyy".I created another measure as below:

Cumulative Quantity = CALCULATE(sum('Table'[Quantity]),filter(ALL('Table'),'Table'[Date]<=max('Table5'[Mth/Yr])))

The second line chart I got this time is shown below:

The second line chart seems close to what I want, except that the month Jan-2020 is missing no matter how I change the formula and Sep-2020 and Oct-2020 is in reverse order.

Could anyone give me a detailed guide on how to create a simple cumulative chart (from Jan-2020 to Dec-2020) based on the data in my first table? Thank you !

[V-lianl-msft · Accepted solution]

Hi @phth017 ,

Please check the following steps as below.

1. Create a calendar table as below and create 1-* relationship with the fact table.

Table 2 = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

2.Create a measure like this:

Cumulative = CALCULATE(SUM('Table'[value]),filter(ALL('Table 2'),'Table 2'[Date]<=max('Table 2'[Date])))

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.

[rkozlowskiTJX]

Thanks for the solution! Are there any benefits to using this method rather than a time intelligence function such as TOTALYTD() ? 

[Anonymous]

I suggest you create a datetable:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Create a relationship with your 'Table' and use the datetable's date in your measure & the "YearMonthShort" from the datetable on your graph (instead of the yearmonth created in your 'Table')

Now all year-month values should be displayed on your visual.

[amitchandak]

@phth017 

Every month is total of last 12 month or Avg, if you want

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

overall cumulative

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

yearly cumulative

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"09/30"))// Year from Oct-sep

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.