calculating a date from an Active Directory value

[KieronMullan]

Hoping someone can help....

I have a Excel spreadsheet connected to our active directory, which pushes out date values such 'Last login' as a power value (e.g. 1.32066E+17).  In excel I had a formula that converted the number into a value to show the correct date.  Excel Formula is [@[user.lastLogon]]/(8.64*10^11)-109205, which when formatted as a date column shows '03/07/2019'

I have connected PBI directly to the same AD source and trying to do the same calculation in PowerBI as a custom column, with the following formula:

Last Login Date = DIVIDE([user.lastLogon],(8.64*10^11)-109205)

However, the date is now showing as the year 2315

Any ideas how to correct this as i would have thought the source values being the same would work out of the box?

Thanks

[KieronMullan]

Thank you - spot on!!!

[Stachu · Accepted solution]

your formula is not the same, you subtract from the denominator in PowerBI, this one is just like in Excel

Last Login Date = DIVIDE ( [user.lastLogon], 8.64 * 10 ^ 11 ) - 109205