Forum Discussion

CoffeeTime's avatar
CoffeeTime
Frequent Visitor
6 years ago
Solved

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 slice...
Need Help
  • danextian's avatar
    danextian
    6 years ago

    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.

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

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