Forum Discussion
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....
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.
v-zhangti
Community Support
Community SupportHi, 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.
Is this the result you expect?
Yes this is exactly what I was looking for, thnk you very much 👍