How to aggregate values in 2 dimensions

[marciocampos]

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?

[AUaero]

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.

[marciocampos]

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

[Anonymous]

[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

[marciocampos · Accepted solution]

It's Working !!! Thank you very much darlove !!!