Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to calculate cumulative of total hours based on ID and Date
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.
Solved! Go to 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.
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.
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
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.
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] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks for the reply!
This dosents seem to work on my data.
I used this:
I solved this by:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |