Forum Discussion
rhaddad87
Helper I
Helper ICumulative Running Total based on column or date
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!
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
2 Replies
Whitewater100Solution Sage
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])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