topic Re: Support using 2 different dates to create the life time. in DAX Commands and Tips

Support using 2 different dates to create the life time.

FelippeAzevedo7 — Mon, 27 May 2024 13:34:58 GMT

I would like to ask for help because I have a report that I am migrating from excel to power bi.
In it I have 2 different dates, one for main and one for activation.
For main I use 2 columns with start and end date, but when it comes to activation I use two other start and end dates.
I tried to reproduce my excel formula and it didn't work very well.
Are there any good tips?

Formula I use in excel: =IF(T5="Activation",INT(Q5-P5)&" days"&TEXT(Q5-P5,"h"" hrs ""m"" mins """),INT(L5-K5)&" days"&TEXT(L5-K5,"h"" hrs ""m"" mins """))

Re: Support using 2 different dates to create the life time.

xifeng_L — Mon, 27 May 2024 15:07:59 GMT

Hi FelippeAzevedo7 ,

Just change the TEXT in your formula to FORMAT, such as:

=IF(T5="Activation",INT(Q5-P5)&" days"&FORMAT(Q5-P5,"h"" hrs ""m"" mins """),INT(L5-K5)&" days"&FORMAT(L5-K5,"h"" hrs ""m"" mins """))

Note: Cell references such as T5 and Q5 need to be changed to the corresponding columns.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Support using 2 different dates to create the life time.

FelippeAzevedo7 — Mon, 27 May 2024 17:56:21 GMT

Hello again

Many tks for the information. It worked perfectly!

Re: Support using 2 different dates to create the life time.

FelippeAzevedo7 — Wed, 29 May 2024 20:54:35 GMT

Hello

Could you check if the formula is correct?

LifeTime = IF(Sheet1[Main or Activation]="Activation",INT(Sheet1[Activation End date].[Date]-Sheet1[Activation Start date].[Date])&" days "&FORMAT(Sheet1[Activation End date].[Date]-Sheet1[Activation Start date].[Date],"h"" hrs ""m"" mins """),INT(Sheet1[Closure Date (UTC)].[Date]-Sheet1[Start Date (UTC)].[Date])&" days "&FORMAT(Sheet1[Closure Date (UTC)].[Date]-Sheet1[Start Date (UTC)].[Date],"h"" hrs ""m"" mins """))

The formula is only showing the days and no hrs or mins.

Could you help me to solve it?

Tks

Re: Support using 2 different dates to create the life time.

xifeng_L — Thu, 30 May 2024 01:46:26 GMT

If Table[Date] is a date containing a time, e.g. 2024-1-1 13:45, then Table[Date]. [Date] then always returns the date part, i.e. 2024-1-1, so that the hours and minutes in the result are 0.

I'm guessing it's due to this