Forum Discussion
calculating a date from an Active Directory value
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
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
2 Replies
- Stachu
Community Champion
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
- KieronMullanFrequent Visitor
Thank you - spot on!!!