Cumulative Running Total based on column or date

[rhaddad87]

Hi, everyone! I'd like to create a cumulative sum column in DAX that calculates the SUM of [FlightHours] from either September to September or everytime [Day/Mo] hits 13 from 17 (The month is split at September 14).   Screenshot below.  Open to any ideas.  Thank you! 

[Anonymous · Accepted solution]

HI @rhaddad87,

You can try to use the following calculate column formula if helps:

formula =
CALCULATE (
    SUM ( Table[FlightHours] ),
    FILTER (
        'Table',
        [Date] <= EARLIER ( Table[Date] )
            && [Date] >= DATE ( YEAR ( EARLIER ( Table[Date] ) ), 9, 1 ) 
            && [Date] < DATE ( YEAR ( EARLIER ( Table[Date] ) ) + 1, 9, 1 )
    )
)

Regards,

Xiaoxin Sheng

[Whitewater100]

Hi:

Please see attached link. Once you have a date table it is easier to do these specialized calculations.

https://drive.google.com/file/d/1oTy2F3vGo_B8e_q8w3YfDA12OrsnV_X_/view?usp=sharing 

You can change the last part of this measure to the year-end date you like.

YTD Fiscal Hours = CALCULATE([Total Flight Hours],

DATESYTD('Date'[Date], "08/31"))

Total Flight Hours = SUM(FlightHours[Flight Hours])