Forum Discussion

kc_'s avatar
kc_
Helper I
2 years ago
Solved

Running / Cumulative Total Resetting Each Month

Hello All 

I would like to create a measure that calculates a running total which resets at the beginning of each month.

 

I am using the following DAX however as you will see it is summing up values from prior periods which is not what I am after:

 

I shall be grateful if someone can assist.

 

Many thanks

 

General Comment
Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    kc_ , Try using below DAX

     

    RunningTotal =
    CALCULATE(
    SUM('YourTable'[YourColumn]),
    FILTER(
    ALL('YourTable'),
    'YourTable'[Date] <= MAX('YourTable'[Date]) &&
    MONTH('YourTable'[Date]) = MONTH(MAX('YourTable'[Date])) &&
    YEAR('YourTable'[Date]) = YEAR(MAX('YourTable'[Date]))
    )
    )

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    kc_ , Try using below DAX

     

    RunningTotal =
    CALCULATE(
    SUM('YourTable'[YourColumn]),
    FILTER(
    ALL('YourTable'),
    'YourTable'[Date] <= MAX('YourTable'[Date]) &&
    MONTH('YourTable'[Date]) = MONTH(MAX('YourTable'[Date])) &&
    YEAR('YourTable'[Date]) = YEAR(MAX('YourTable'[Date]))
    )
    )