Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX: Cummulative count per hour

Hi PBIX community, I'm looking to show cummulative count in a table. The cummulative count works fine until 11PM but then it kind of goes awry. I have date and hour table as dimentions. Date and hou...
  • ERD's avatar
    ERD
    5 years ago

    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.

ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous ,

I didn't check the figures for other measures (as far as I've understood, you only have issues with cummulative measure) and according to your visual you can change the measure this way:

Cummulative Eq Assign_2 = 
VAR currentDate = MAX ( d_DateTable[Date] )
VAR currentTime = MAX ( d_HourTable[Hour of Day] )
VAR result =
    CALCULATE (
        [EqAssignPerHour],
        FILTER ( 
            ALLSELECTED ( d_DateTable[Date] ), 
            d_DateTable[Date] <= currentDate 
        ),
        FILTER (
            ALLSELECTED ( d_HourTable[Hour of Day] ),
            d_HourTable[Hour of Day] <= currentTime
        )
    )
RETURN
    IF ( NOT ISBLANK ( [EqAssignPerHour] ), result )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.