Forum Discussion

phth017's avatar
phth017
Regular Visitor
6 years ago
Solved

Creating Cumulative Line Chart

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 !
Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

     

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      rkozlowskiTJX
      Regular Visitor
      3 years ago

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