PBI Desktop bug?: Calculated column of date + time => 1 day off in PBI

[WP]

Hi I have following issue in PBI desktop (April release) : when creating calculated column using a date and time column, the result is one day off.

When using the same data source in PowerPivot Excel, using the same formula, the result is as expected.

E.g.

Date field reads : April 12, 2017 12 AM

Time field reads: 1/1/1900 1 AM

when adding both in PBI, result becomes April 13 1AM

in Excel April 12 1AM (which is the correct result)

Anybody any idea how to solve this, because a lot of my calculations depend on having correct timestamps.

thank you!

[v-qiuyu-msft]

Hi @WP,

How did you create a calculated column to combine date and time column? Based on my test in desktop version 2.45.4704.442, use below DAX can return correct datetime value:

DateTime = DATEVALUE(FORMAT('Table7'[Date],"Short Date") & " " & FORMAT('Table7'[Time],"Medium Time"))

Best Regards,
Qiuyun Yu

[WP]

Hi, Qiuyun thank you for your reply.

Since the fields are already imported and recognized as date/time values by PowerBI, I just add both columns together

date field+time field

Since this gives the desired result in Excel Powerpivot I would think this should work as well in PBI desktop?

I tried your workaround and it only returns the date field, it does not add the time field.

below is copy of the formula . I used it with both medium and long time, but to no avail.

timestamp_lastmodified = Datevalue(format(WorkItemInfo'[Work Item Last Modified Date],"Short Date")&" "&format(WorkItemInfo'[Work Item Last Modified Time],"Long Time"))

[WP]

Hi,

I have noticed that the difference is coming from the fact that originally columns are recognized as date/time values. When I change the format from date/time to date and time respectively, the calculations are correct.

Notwithstanding that in Powerpivot in Excel (2013) this distinction is not there.

 So my problem is solved now, by changing the formats after import to either date or time instead of using date/time.

When date/time is selected for both fields, another day is added to the result.

Thx for the support.