topic Re: Transforming a date related Excel formula into DAX in DAX Commands and Tips

Transforming a date related Excel formula into DAX

MXSven — Wed, 08 Jun 2022 09:47:03 GMT

Hi Everybody,

I am struggeling with the translation of an Excel formula "StartDate" into Power BI (DAX)

StartDate = "="&(Today()-DATE(1970;1;1))*60*60*24,
EndDate = "=1646092800"

It appears to me that the "date" & "today" formula concept of excel, which returns an according serial number

doesn't exist in PowerBi

Re: Transforming a date related Excel formula into DAX

Anonymous — Thu, 09 Jun 2022 10:18:10 GMT

TODAY function (DAX) - DAX | Microsoft Docs

DATE function (DAX) - DAX | Microsoft Docs

Re: Transforming a date related Excel formula into DAX

MXSven — Fri, 10 Jun 2022 08:46:17 GMT

Thanks,
but I don't got it to work

StartDate = "="&((Date(2022,6,10)-DATE(1970,1,1))*60*60*24)

doesn't recognize Date/DATE

Any hints

Re: Transforming a date related Excel formula into DAX

Anonymous — Fri, 10 Jun 2022 08:52:38 GMT

StartDate = (Today()-DATE(1970,1,1))*60*60*24

Re: Transforming a date related Excel formula into DAX

MXSven — Fri, 10 Jun 2022 09:03:49 GMT

I put it exactly like this,
but today and date couldn't be recognized

Re: Transforming a date related Excel formula into DAX

Anonymous — Fri, 10 Jun 2022 09:14:17 GMT

In Power BI, create a new measure

StartDate = (Today()-DATE(1970,1,1))*60*60*24

Re: Transforming a date related Excel formula into DAX

MXSven — Fri, 10 Jun 2022 09:25:15 GMT

Thanks for your fast reply

but how do get the measure then into:

Re: Transforming a date related Excel formula into DAX

Anonymous — Fri, 10 Jun 2022 09:58:36 GMT

Ohhhh..

I know, in Power Query , you should use m language, not dax

StartDate = "=" & Text.From(Duration.TotalSeconds(DateTime.From(Date.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), 0))) - #datetime(1970, 1, 1, 0, 0, 0)))

change the 0 to your timezone

DateTimeZone.SwitchZone - PowerQuery M | Microsoft Docs

Re: Transforming a date related Excel formula into DAX

MXSven — Fri, 10 Jun 2022 11:33:08 GMT

sorry I didn't know that

your solution works perfect

I wouldn't have been able to solve this on my own with Query M.

Thanks