Incorrect duration of days using Duration in power bi query

[Anonymous]

Hi All,

I am facing this issue where I am using "Duration. Days" to calculate aging of tickets so my tickets data has an attribute "sys_created_on"  and the days are calculated in the power query using the local now function.

#"Added Custom" = Table.AddColumn(#"Expanded DimSupportGroup_GlobalSupport", "Ageing", each Duration.Days(DateTime.LocalNow()-[sys_created_on]))

above is the statement in m query calculating duration in days but the issue is the duration is not accurate for eg. for 7/30/2020 the duration should be one day but I am getting 0 in the table.

Is there anything I am missing here. Or since I am using the Local now the days are calculated based on my system settings. If so how can make this calculation independent of local time?

[MattAllington]

You cannot debug Problems if you can't see the interim results. Add dateTime.localnow() to a column so you can see it. Work out the difference manually. Duration.days extracts the full days portion. 23:59:59 = 0. 24:00:00 = 1

[amitchandak]

@Anonymous , is it consistent?  Typically all date function does not consider < 24 hours a day. Like in datediff diff between 30 Jul -30jul i 0 in terms of the day. This could be 1 day present in HR. Typically we end up adding +1 in such cases

[Anonymous]

Thanks maybe I understood the cause is related to time along with dates and yes for some values it's calculating the correct results

[dax · Accepted solution]

Hi @Anonymous , 

Yes, you could refer to @MattAllington 's reply, so if you want to get date difference and ignore time in date, I think you could try below M code

= Table.AddColumn(#"Added Custom1", "Custom.2", each DateTime.Date(DateTime.LocalNow())-DateTime.Date([Column1]))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.