Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax calculation for Time duration

Hi team,

I need your help with a Dax/power M calculation. My week start day is Thursday and my end week day is Wednesday. I have the following data:

Date

Weekday

Custom Week Num

TASK

Task Start

Task end

Current Duration (h)

Needed duration (h)

01.02.2022

Tuesday

4

ER

01.01.2022 01:00:00 AM

01.01.2022 03:10:00 AM

02:10

02:10

02.02.2022

Wednesday

4

TO

02.01.2022 23:20:00 PM

03.02.2022 01:00:00 AM

01:40

40min (if week 4 is selected)
1h if Week 5 Is selected

03.02.2022

Thursday

5

EE

15:10:00 PM

18:10:15 PM

03:00

03:00

 

I’m using a slicer for a Custom Current week. So, if Week 4 is selected, I see dates from 27.01.2022 until 02.02.2022 incl. In Week 5 I will see dates 03.02.2022-09.02.2022 incl. The issue I have is column Duration which shows the full duration between Task Start and Task end. In 02.02 Wednesday I have a task which starts in 23:20 PM (date 02.02 Week 4), but it ends in the next day, which is in the new Week 5. The duration is 40mins in 02.02 Week 4, and 01h in 03.02 Week 5.

I need the following new duration column:
If I select week 4, in row 02.02 Wednesday I would like to see Duration 40min, and If I select Week 5, in 02.02 Wednesday I would like to see Duration 01h.

If the Task start and Task end dates are in the same week, the Duration should be the full one, but if the Task start and Task end dates are in different week, I would like to see the Duration only for the selected week.

I will apreciate the help. Thank you.

Need Help
Show and Tell
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

    Measure = 
var _start=MAX('Table'[Task Start])
var _end=MAX('Table'[Task end])
return 
IF(ISFILTERED('slicer'[Custom Week Num]),IF(DATEDIFF(_start,_end,DAY)=0,_end-_start,
IF(SELECTEDVALUE('slicer'[Custom Week Num])=CALCULATE(MAX([Custom Week Num]),FILTER('Table',DATEDIFF([Date],[Task Start],DAY)=0)), MAX([Date])+1-_start,_end-MAX([Date])-1))
 ,_end-_start)

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    "I need the following new duration column"

     

    If you have filters/slicers that are impacting your result then your result cannot be a column. It has to be a measure.  Columns are calculated BEFORE user interaction.

     

    What is the expected granularity of your result - single minutes, tens of minutes?  Your decision here will impact the computation. You need to use INTERSECT and COUNTROWS to get to your results.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    Try it.

    Measure = 
var _start=MAX('Table'[Task Start])
var _end=MAX('Table'[Task end])
return 
IF(ISFILTERED('slicer'[Custom Week Num]),IF(DATEDIFF(_start,_end,DAY)=0,_end-_start,
IF(SELECTEDVALUE('slicer'[Custom Week Num])=CALCULATE(MAX([Custom Week Num]),FILTER('Table',DATEDIFF([Date],[Task Start],DAY)=0)), MAX([Date])+1-_start,_end-MAX([Date])-1))
 ,_end-_start)

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.