Forum Discussion

Thierry7890's avatar
Thierry7890
Frequent Visitor
3 years ago
Solved

Excel formula to Dax

Hello All,

 

I am quite new to Power BI and DAX and I have tried to find solutions online but I can't find what I need so I would greatly appreciate if someone could help me in the right direction.

 

I'm working with data from SAP where I want to calculate the duration between confirmation times that are in ascending order in 1 column. Right now i do these calculations in Excel but I would like to do this in Power BI. 

 

The excel formula is as follows 

=IF(AND(B2=B1;A2=A1);LET(D;D2-D1;T;$H$1;IF(D>T;T;D));0)

which in words means 

IF(AND(UserCurrentLine=UserPreviousLine;DateCurrentline=DatePreviousLine);LET(D(Duration);ConformationTimeThisline-ConformationTimePreviousLine;T(Limit before it's considered downtime);00:10:00;IF(D>T;T;D));0)

 

So evaluated on a line lvl I check if the User and de Date are the same as in the previous line and if so then I want to take the conformation time of this line minus the conformation time of the last line and then I know the time spend on the activity, If the time spend is more then 10 minutes (00:10:00) then I want it to return 10 minutes and if the User or the Date is different I want it to return 0.

 

The data I us looks as follows

DateUserMovement TypeConfirmation timeDuration
01/12/2022WH200-USER1091317:47:1700:00:40
01/12/2022WH200-USER1091317:48:0200:00:45
01/12/2022WH200-USER1091317:48:4500:00:43
01/12/2022WH200-USER1091317:50:2700:01:42
01/12/2022WH200-USER1091317:51:2200:00:55
01/12/2022WH200-USER1091317:52:2600:01:04
01/12/2022WH200-USER1091317:53:1000:00:44
01/12/2022WH200-USER1091317:53:2700:00:17
01/12/2022WH200-USER1160107:58:3800:00:00
01/12/2022WH200-USER1160107:59:3300:00:55
01/12/2022WH200-USER1160108:01:1400:01:41
01/12/2022WH200-USER1160108:01:3500:00:21
01/12/2022WH200-USER1160108:08:4500:07:10
01/12/2022WH200-USER1160108:09:4000:00:55
01/12/2022WH200-USER1160108:21:1000:10:00
01/12/2022WH200-USER1160108:27:3500:06:25
01/12/2022WH200-USER1160108:34:5900:07:24
01/12/2022WH200-USER1160108:43:0600:08:07
01/12/2022WH200-USER1160108:49:4500:06:39
01/12/2022WH200-USER1160108:54:5100:05:06
01/12/2022WH200-USER1160109:20:3500:10:00
01/12/2022WH200-USER1160109:27:3300:06:58

 

thanks in advance

 

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Thierry7890 

     

    You can try the following methods. Start by adding an index column to Power Query.

    Column:

    Previous Conformation Time = CALCULATE(MAX('Table'[Confirmation time]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
    Diff = 
Var _Second=DATEDIFF([Previous Conformation Time],[Confirmation time],SECOND)
Var _Previousdate=CALCULATE(MAX('Table'[Date]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
Var _Previoususer=CALCULATE(MAX('Table'[User]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
Return
IF(_Second>=600,600,IF([Date]=_Previousdate&&[User]=_Previoususer,_Second,0))
    Duration = TIME(0,0,[Diff])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Thierry7890 

     

    You can try the following methods. Start by adding an index column to Power Query.

    Column:

    Previous Conformation Time = CALCULATE(MAX('Table'[Confirmation time]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
    Diff = 
Var _Second=DATEDIFF([Previous Conformation Time],[Confirmation time],SECOND)
Var _Previousdate=CALCULATE(MAX('Table'[Date]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
Var _Previoususer=CALCULATE(MAX('Table'[User]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
Return
IF(_Second>=600,600,IF([Date]=_Previousdate&&[User]=_Previoususer,_Second,0))
    Duration = TIME(0,0,[Diff])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Thierry7890's avatar
      Thierry7890
      Frequent Visitor
      3 years ago

      Is this the result you expect?

      Yes this is exactly what I was looking for, thnk you very much 👍

    • Thierry7890's avatar
      Thierry7890
      Frequent Visitor
      3 years ago

      Thanks for the link, I've added it to my favourites to learn more in the future 👍