Excel formula to Dax

[Thierry7890]

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

Date	User	Movement Type	Confirmation time	Duration
01/12/2022	WH200-USER10	913	17:47:17	00:00:40
01/12/2022	WH200-USER10	913	17:48:02	00:00:45
01/12/2022	WH200-USER10	913	17:48:45	00:00:43
01/12/2022	WH200-USER10	913	17:50:27	00:01:42
01/12/2022	WH200-USER10	913	17:51:22	00:00:55
01/12/2022	WH200-USER10	913	17:52:26	00:01:04
01/12/2022	WH200-USER10	913	17:53:10	00:00:44
01/12/2022	WH200-USER10	913	17:53:27	00:00:17
01/12/2022	WH200-USER11	601	07:58:38	00:00:00
01/12/2022	WH200-USER11	601	07:59:33	00:00:55
01/12/2022	WH200-USER11	601	08:01:14	00:01:41
01/12/2022	WH200-USER11	601	08:01:35	00:00:21
01/12/2022	WH200-USER11	601	08:08:45	00:07:10
01/12/2022	WH200-USER11	601	08:09:40	00:00:55
01/12/2022	WH200-USER11	601	08:21:10	00:10:00
01/12/2022	WH200-USER11	601	08:27:35	00:06:25
01/12/2022	WH200-USER11	601	08:34:59	00:07:24
01/12/2022	WH200-USER11	601	08:43:06	00:08:07
01/12/2022	WH200-USER11	601	08:49:45	00:06:39
01/12/2022	WH200-USER11	601	08:54:51	00:05:06
01/12/2022	WH200-USER11	601	09:20:35	00:10:00
01/12/2022	WH200-USER11	601	09:27:33	00:06:58

thanks in advance

[v-zhangti · Accepted solution]

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]

Is this the result you expect?

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

[Greg_Deckler]

@Thierry7890 See if this helps: Excel to DAX Translation - Microsoft Power BI Community

[Thierry7890]

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