how to generate cumulative totals?

[monkeyintheeast]

I have a dataset of hours worked per month, and i'd like to plot a bar and line graph showing hours per month on the bar chart, and cumulative hours on the line graph.

I've two questions that I would be greateful of assistance with please...

1. The x-axis of my chart only goes from January to December. How do i get it to show the full three year dataset?

2, What is the best way to generate the cumulative line? I'm new to this, so i'd be grateful of the education please!

Thank you

[amitchandak]

@monkeyintheeast , You Should use month year on your x- axis

In case you have date else have these

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm") // Sort month year on this format.

It better to have a date table or month table with month rank or YYYYMM year

Example of Cumulative

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

Or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[Month Year sort] <=max(date[Month Year sort])))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

[Anand24 · Accepted solution]

Hi @monkeyintheeast ,

Ans 1: You can use both year and date from the date for getting all 36 months in the X-axis.

Ans 2: You can create a new measure with below dax for cumulative total and use this new measure in line chart:

New Measure=

CALCULATE(

Sum(Hours),

FILTER(

ALLSELECTED(Date),

Date <= MAX(Date)

)

)

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!