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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.