DAX: Cummulative count per hour

[sam0007]

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

[sam0007]

That worked! Thank you!

[sam0007]

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 · Accepted solution]

@sam0007 ,

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.

[sam0007]

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

[ERD]

Hi @sam0007 ,

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.