topic Re: Datediff between two times in DAX Commands and Tips

Datediff between two times

Anonymous — Wed, 08 May 2019 09:12:47 GMT

Hi,

I am new to powerbi i have created a dax to calculate the difference between start and end time.

kpi = SWITCH(TRUE();

project[Start time]<project[End time];DATEDIFF([Start time]; [End time];HOUR);

[Start time]>[End time]; DATEDIFF(project[End time];project[Start time];HOUR);0)

My start time is 7-05-2019 23:00 and End time 7-05-2019 05:00 the difference between the two times is 6 hours but it gives me an output of 18. Can some one help me out by fixing this issue thank you!!

Re: Datediff between two times

HotChilli — Wed, 08 May 2019 20:10:29 GMT

There's definitely 18 hours between 5 am and 11pm on the same day

Re: Datediff between two times

Anonymous — Thu, 09 May 2019 06:35:55 GMT

My mistake what i meant was. The hours that are in between 11pm to 5am are 6 hours.

But from my Dax is get an answer of 18 hours. HotChilli

Re: Datediff between two times

HotChilli — Thu, 09 May 2019 10:53:47 GMT

You'll have to clear this up before proceeding. The sample data provided is "My start time is 7-05-2019 23:00 and End time 7-05-2019 05:00". What is the real data?

Re: Datediff between two times

Anonymous — Thu, 09 May 2019 14:04:52 GMT

Thank you for your reply. I think i found the problem if you get the data from sharepoint. Powerbi Default local time for netherlands in summer time is +2 hours that is why it is giving me the wrong output in powerbi.

= Table.AddColumn(#"Namen van kolommen gewijzigd", "LocalDateTime", each DateTime.AddZone([Begin tijd],2))

And the mistake for 23:00 to 05:00 was giving me 18 hours is because the sharepoint list was in text not in data/time. Now i set it to the right field and gave me 6 hours in between. HotChilli