Forum Discussion
Running Total Comparing Months
I am trying to plot a visual that compares tickets during each month, day by day. I was able to create it on a non cumulative view, but the running total for each month won't work.
I used a slicer to filter this year's period (2020+) and created a visual I would like to see each month's evolution comparison. I couldn't get to my desired result, because each month's count starts from the previous end and I would like to start from 0.
The first visual is OK. The problem is with the second one. As you can see, the month's initial value is not zero as I would like.
Hi CoffeeTime ,
Create a calculated column for the day of the month
Day of Month = DAY ( 'Table'[Dates] )Then create this measure
MTD = CALCULATE ( [Sum of Values], FILTER ( ALL ( 'Table'[Day of Month] ), 'Table'[Day of Month] <= MAX ( 'Table'[Day of Month] ) ) )The reason why your formula is not returning the expected result is because you are using [Date] in your filter so the cumulative sum is always from the earliest date in the current filter context until the current date in your line chart instead of actually from the start of the month.
6 Replies
CoffeeTime add +0 at end of your measure and then test it. Would appreciate Kudos 🙂 if my solution helped.
Hi CoffeeTime ,
Create a calculated column for the day of the month
Day of Month = DAY ( 'Table'[Dates] )Then create this measure
MTD = CALCULATE ( [Sum of Values], FILTER ( ALL ( 'Table'[Day of Month] ), 'Table'[Day of Month] <= MAX ( 'Table'[Day of Month] ) ) )The reason why your formula is not returning the expected result is because you are using [Date] in your filter so the cumulative sum is always from the earliest date in the current filter context until the current date in your line chart instead of actually from the start of the month.
Hi! I have a similar issue. I used what you provided above and that worked! However, how do I get it to automatically same time last year each time? I manually selected the same time last year. The table this uses does update every 13 months. I am thinking if I have it selected it will automatically update that month upon refresh?
Hi,
Could you share some data and show the expected result. Show the result in a simple Table format. From there, we can always build any chart we want.
If you want Cumulative month, then prefer daysmtd or totalmtd
Example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr)))) MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date]))) MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))My Calendar is Date
I was trying to do the same thing as OP and for some reason danextian's solution didn't work for me: it just showed the daily totals for each day of the month, not the MTD running total.
So instead I used this measure:
Earnings MTD = IF(MAX('Date Table'[Date])>TODAY(),BLANK(), TOTALMTD(SUM('Project'[InvoiceAmount]),'Date Table'[Date]) )Then on my graph I had my date variable on the x axis, in Series I had a variable for the month (filtered to just the current and previous month) and then the measure above as Values.
