topic Re: Calculate cumulative Total hours based on ID and date in DAX Commands and Tips

Calculate cumulative Total hours based on ID and date

WTAS80486 — Mon, 08 Aug 2022 22:26:55 GMT

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.

Re: Calculate cumulative Total hours based on ID and date

WTAS80486 — Tue, 09 Aug 2022 03:16:04 GMT

I solved this by:

Culmulative hours = CALCULATE (

SUM('Table'[Hours]),

FILTER (

ALL ( 'Table'),

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

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

)

Re: Calculate cumulative Total hours based on ID and date

Jihwan_Kim — Tue, 09 Aug 2022 03:19:14 GMT

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] ) ) ) )

Re: Calculate cumulative Total hours based on ID and date

WTAS80486 — Tue, 09 Aug 2022 04:04:38 GMT

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])

)

Re: Calculate cumulative Total hours based on ID and date

WTAS80486 — Tue, 09 Aug 2022 12:52:05 GMT

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

Re: Calculate cumulative Total hours based on ID and date

selinaz — Thu, 11 Aug 2022 06:03:25 GMT

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.

Re: Calculate cumulative Total hours based on ID and date

v-yanjiang-msft — Wed, 17 Aug 2022 08:41:51 GMT

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.