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 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?
Solved! Go to Solution.
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
[Working Time] =
var __onlyOneRegionVisible = hasonevalue( T[Region] )
var __differentTimeBounds =
summarize(
T,
T[Start],
T[Finish]
)
var __workingTime =
sumx(
__differentTimeBounds,
T[Finish] - T[Start]
)
return
if(
__onlyOneRegionVisible,
__workingTime
)
Best
Darek
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |