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 hour table are related to data table via inactive relationships. I have used 'userelationship' functions in my DAX to count instances by hour.

 

Attached is PBIX file and results that I'm expecting in an excel file.

 

* How do I navigate to resolve the cummulative total issue?

* Why is there a value of '8' in cumulative column on 13 July 2021?

* How can I get rid of cummulative data for 14 July 2021?

 

DAX for cummulative is:

Cummulative Eq Assign =
CALCULATE (
[TotalLoadWithEq],
USERELATIONSHIP ( f_DataTable[Eq Assign Date], d_DateTable[Date] ),
USERELATIONSHIP ( f_DataTable[Eq Assign Hour], d_HourTable[Time] ),
FILTER (
ALLSELECTED ( d_DateTable ),
d_DateTable[Date] <= MAX ( d_DateTable[Date] )
),
FILTER (
ALLSELECTED ( d_HourTable[Time] ),
d_HourTable[Time] <= MAX ( d_HourTable[Time] )
)
)

 

Any hints or tips to resolve this error would be helpful. Thanks a lot in advance!

 

https://www.dropbox.com/sh/l94j7rb9iavm783/AADqJs6dOZU06anbmdfftyiUa?dl=0

  • 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.

5 Replies

  • 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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    ERD Thanks very much again! learning a lot from you. Cheers!

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    That worked! Thank you!