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...
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.

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.