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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    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.