cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marciocampos
Regular Visitor

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

RegionWaveTest NameStartFinish
RJ1A17/07/201918/07/2019
RJ1B18/07/201919/07/2019
RJ2A25/07/201926/07/2019
SP1A15/07/201916/07/2019
SP1B17/07/201919/07/2019

 

Result Expected:

RegionTotal Time
(days)
Working Time
(days)
RJ93
SP43

 

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?

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
AUaero
Advocate II
Advocate II

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:

 

RegionWaveTest NameStartFinish
RJ1A17/07/201918/07/2019
RJ1B17/07/201918/07/2019
RJ2A25/07/201926/07/2019

 

Working Time (RJ) = (18/07/2019 - 17/07/2019) + (26/07/2019-25/07/2019) = 2 days

Anonymous
Not applicable

[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

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

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors