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
5 years ago

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?

 

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

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.