Datediff between two times

[Anonymous]

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!!

[HotChilli]

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

[Anonymous]

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 

[HotChilli]

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?

[Anonymous · Accepted solution]

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