Forum Discussion

KieronMullan's avatar
KieronMullan
Frequent Visitor
7 years ago
Solved

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's avatar
    Stachu
    Icon for Community Champion rankCommunity 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