Forum Discussion
How to aggregate values in 2 dimensions
hi folks!
I need some help here !! Someone could help me? :)
I have a table with "test records" and I need to extract from it the "test total duration" and "days worked per test wave".
See the table (fATP):
| Region | Wave | Test Name | Start | Finish |
| RJ | 1 | A | 17/07/2019 | 18/07/2019 |
| RJ | 1 | B | 18/07/2019 | 19/07/2019 |
| RJ | 2 | A | 25/07/2019 | 26/07/2019 |
| SP | 1 | A | 15/07/2019 | 16/07/2019 |
| SP | 1 | B | 17/07/2019 | 19/07/2019 |
Result Expected:
| Region | Total Time (days) | Working Time (days) |
| RJ | 9 | 3 |
| SP | 4 | 3 |
Total Time (RJ) = 26/07/2019 - 17/07/2019 = 9 days
Total Time (SP) = 19/07/2019 - 15/07/2019 = 4 days
for this metric i used the follow DAX expression: (working)
Total Time = DATEDIFF(MIN(fATP[Start]);MAX(fATP[Finish]);DAY)
but I couldn't make a DAX expression to get value by "working time".
Working Time (RJ) = (18/07/2019 - 17/07/2019) + (19/07/2019-18/07/2019) + (26/07/2019-25/07/2019) = 3 days
Working Time (SP) = (16/07/2019 - 15/07/2019) + (19/07/2019-17/07/2019) = 3 days
Any idea how to solve this?
- It's Working !!! Thank you very much darlove !!!
4 Replies
- AUaero
Responsive Resident
Why not create a calculated column to calculate the days between start and finish for each row? Working Days would be the sum of the the calculated column.
This is not possible because we can have 2 tests performed on the same day and in this situation we have counted only 1 working day and not 2. See the example below:
Region Wave Test Name Start Finish RJ 1 A 17/07/2019 18/07/2019 RJ 1 B 17/07/2019 18/07/2019 RJ 2 A 25/07/2019 26/07/2019 Working Time (RJ) = (18/07/2019 - 17/07/2019) + (26/07/2019-25/07/2019) = 2 days
