Forum Discussion
DAX: Cummulative count per hour
Anonymous ,
I'm not sure this is the best implementation since I'm not aware about your real model and all the prerequisites, but you can try this option:
Cummulative Eq Assign_2 = VAR currentDate = MAX ( d_DateTable[Date] ) VAR currentTime = MAX ( d_HourTable[Hour of Day] ) VAR prevDayValue = IF ( ISINSCOPE ( d_DateTable[Date] ), CALCULATE ( [EqAssignPerHour], FILTER ( ALL ( d_DateTable[Date] ), d_DateTable[Date] < currentDate ), ALL ( d_HourTable[Hour of Day] ) ) ) VAR c_amt = CALCULATE ( [EqAssignPerHour], FILTER ( ALL ( d_HourTable[Hour of Day] ), d_HourTable[Hour of Day] <= currentTime ) ) RETURN IF ( NOT ISBLANK ( [EqAssignPerHour] ), prevDayValue + c_amt )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Sorry, have a small issue here, on Tuesday 13 Jul 2021 cummulative column shows as 11 it should be 41 because:
Cummulative count 30 on Monday 12 Jul 2021 @ 11 PM
+
11 new equipment on Tuesday 13 Jul @ 1 AM.
resulting in 41 for cummulative column at 1AM.
Thanks!
Community ChampionAnonymous ,
I'm not sure this is the best implementation since I'm not aware about your real model and all the prerequisites, but you can try this option:
Cummulative Eq Assign_2 =
VAR currentDate = MAX ( d_DateTable[Date] )
VAR currentTime = MAX ( d_HourTable[Hour of Day] )
VAR prevDayValue =
IF (
ISINSCOPE ( d_DateTable[Date] ),
CALCULATE (
[EqAssignPerHour],
FILTER ( ALL ( d_DateTable[Date] ), d_DateTable[Date] < currentDate ),
ALL ( d_HourTable[Hour of Day] )
)
)
VAR c_amt =
CALCULATE (
[EqAssignPerHour],
FILTER (
ALL ( d_HourTable[Hour of Day] ),
d_HourTable[Hour of Day] <= currentTime
)
)
RETURN
IF ( NOT ISBLANK ( [EqAssignPerHour] ), prevDayValue + c_amt )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.