Creating Line Chart with cumulative sum by month over a FY

[phth017]

Hi

I have the data in a Table shown below (left side), with the item, quantity and date entered. I would like to create a line chart showing the cumulative quatity over the year by month. Currently, I am doing it manually to sum the cumulative quantity and would need to create another table (right side) before I could plot the chart out. Does anyone know how can I create the same line chart in Power BI ? Thank you !

[phth017]

Hi everyone

Thanks for your reply but I still couldn't get the desired outcome.

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 line chart does not display the month September & December because I do not have any data for these two months.

Then I tried with the second method to create a new table (named Table5) with 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 to be what I want, except that the month Jan-2020 is missing no matter how I change the formula.

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

[v-alq-msft]

Hi, @phth017 

I'd like to suggest you use CALENDAR or CALENDARAUTO function to create a date table. Then you need to associate two table with date column and put the date column from date table in the 'Axis'.

You may try the measure like below.

Example1 = CALCULATE(SUM(Sales[Quantity]),DATESYTD(DateTime[DateKey],"6/30"))

or

Example2 = TOTALYTD(SUM(Sales[Quantity]), DateTime[DateKey],"6/30")

Generally, your data model will contain a calendar table. It is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly.

Best Regards

Allan

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

[amitchandak]

@phth017 , Your screenshot are missing.

Please make sure you are using the month year from the date table.  What @parry2k  has mentioned

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])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))
within FY
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

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.

[parry2k]

@phth017 when you are visualizing the data, make sure month/year column is used from date dimension not your transaction table.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k]

@phth017 there are tons of posts on cumulative 

quantity but the key is to add a date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

and this measure will do it

Cumulative Qty = 
CALCULATE ( 
SUM ( Table[Quantity] ),
FILTER ( 
ALLSELECTED ( DateTable[Date] ),
DateTable[Date] <= MAX ( DateTable[Date] ) 
)
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[phth017]

Hi @parry2k 

I followed the steps and got the cumulative chart plotted. However, the X-axis which is the Mth/Yr does not display the month with empty data (it is somehow skipped). Any idea how can I display all 12 months within the chart as similar to what shown in my first post?