Calculate cumulative Total hours based on ID and date

[WTAS80486]

Hi,

I am trying to calculate cumulative of total hours based on ID and Date

Cumulative Run Hours = CALCULATE( SUM('Table'[Hours]),

FILTER(

ALL('Table'[ ID]),

'Table'[Date] <=MAX('Table'[Date)]

)

))

Nothing seems to work. ALL, ALLEXCEPT, EARLIER

ID	Date	Hours	Cumulative
A	11/9/2021	3	3
A	12/1/2021	6	9
A	1/2/2022	9	18
B	2/3/2022	2	2
B	3/4/2022	4	6
B	4/5/2022	8	16
C	5/3/2022	3	3
C	6/2/2022	4	7
C	7/1/2022	4	14

Can someone please help.

[v-yanjiang-msft · Accepted solution]

Hi @WTAS80486 ,

According to your description, here's my solution.

Create a Date table.

Date = CALENDAR(DATE(2021,1,1),DATE(2022,12,31))

Create relationship between the two tables with the Date column. Then create a measure.

Cumulative =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ID] = MAX ( 'Table'[ID] )
            && 'Table'[Date] <= MAX ( 'Date'[Date] )
    )
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[WTAS80486]

I am trying to solve the same thing on measure from another table and get cumulative counts of failures based on month in another column of another table. But getting error

[selinaz]

Hi @WTAS80486 ,

So what are the error you encountered? Do you have sample data from another table or a PBIX file (without private data)?

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

Best regards.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Cumulative Run Hours =
IF (
    HASONEVALUE ( 'Table'[ID] ),
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
        )
    )
)

[WTAS80486]

Thanks for the reply!

This dosents seem to work on my data.

I used this:

Culmulative hours = CALCULATE (

                SUM('Table'[Hours]),

                FILTER (

                    ALL ( 'Table'),

                    'Table'[ ID] = EARLIER ( 'Table'[ ID] )

                        && 'Table'[Date]<= EARLIER ('Table'[Date])

                )

            )

[WTAS80486]

I solved this by:

Culmulative hours = CALCULATE (

                SUM('Table'[Hours]),

                FILTER (

                    ALL ( 'Table'),

                    'Table'[ ID] = EARLIER ( 'Table'[ ID] )

                        && 'Table'[Date]<= EARLIER ('Table'[Date])

                )

            )