Finding Min and Max Dates based on list of users

[CYParker]

Hi All,

I've had a look through google results in the last couple of days and haven't found anything which seems to point me in the right direction so hopefully someone here can.

I'm building a dashboard based on Dyn 365 audit data and I would like to be able to identify the min (earliest) and max (latest) date which users have accessed the system, and have this information saved in a table with a list of users.

I have a system users list in a table named systemusers and the audit data which has the unfiltered/sorted information (sample further down below) in another table named audit.

What I'm thinking is two new columns in the systemusers table which contain the min and max dates with regards to the users' system access (see table just below). I've done this in excel easily enough using pivot tables and getpivotdata, but can't work out how to do it in Power BI.

User Name	Min Date	Max Date
Hichelle Mowden
Myn Lorris
Hhomas Tolden
Perry Karker
Blan Aird
Kylie Scheuboeck
Manessa Voore
Fred Power

I was trying to accomplish the following using formulas:

- In the Min Date column

  1) Look for all rows in the audit table which have "Hichelle Mowden" as the record

  2) Compare all the associated 'Dates' of those rows to find the date with the lowest value

  3) Store the date in the respective Min Date cell

  4) Move to the next row and repeat steps 1 and 2

- In the Max Date column

  1) Look for all rows in the audit table which have "Hichelle Mowden" as the record

  2) Compare all the associated 'Dates' of those rows to find the date with the highest value

  3) Store the date in the respective Max Date cell

  4) Move to the next row and repeat steps 1 and 2

I've tried a few different formula combinations but don't know enought about DAX formulas to find anything as yet. Anyone have any ideas?

Sample source data

[v-shex-msft · Accepted solution]

Hi @CYParker ,

You can use following calculate column formula to find out correspond min/max date based on current user:

MINDate =
CALCULATE (
    MIN ( Table[Date] ),
    FILTER ( ALL ( Table ), [Record] = EARLIER ( Table[Record] ) )
)

MAXDate =
CALCULATE (
    MAX ( Table[Date] ),
    FILTER ( ALL ( Table ), [Record] = EARLIER ( Table[Record] ) )
)

Regards,

Xiaoxin Sheng

[CYParker]

Thanks @v-shex-msft! Worked perfectly