Earliest date for employee - Power Query Editor

[Lucjan_Szulik]

Hi Guys,

I am sure that an identical question was already raised, but I am quite new to Power Query Editor, hence I don't really know how to search for the answer. I have tried few solutions, but nothing seem to work (or I cannot really do it).

Anyway: data form SQL server, after some modeling we have:

Column on the left: employee ID (multiple for the same ID)

Column on the right: date (different for each line per ID)

What I would need to have in the 3rd column is minimum date value that appears for the given ID.

Thanks,

Lucjan

[v-jiascu-msft]

Hi @Lucjan_Szulik,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[MFelix]

Hi @Lucjan_Szulik,

Why fo you need to create a 3rd column?

If you only want to know what is the minimum date for a particular ID when you add it to a visual you can select the sumarization as MINIMUM, other way is to create a measure:

Minimum Date = MIN(Table[begin_valid_date])

And add it to your visuals then based on context if you add the ID's it will return the minimum value of the context given.

Regards,

MFelix

[Lucjan_Szulik]

Hi,

Need it for calculating absenteeism per seniority. 

I have another table with scheduled hours and all the present/absenteeism. I need to add seniority of every agent on the given day and base on that build some visualizations.

Thanks,

Lucjan

[MFelix · Accepted solution]

Hi @Lucjan_Szulik,

Don't know what you want to calculate but believe that the best way is to use measures because calculated columns have additional space to your model and complexity.

But the options you have for creating the column are DAX and M language see below both options.

DAX Calculated column:

Minimum Date Dax = 
CALCULATE (
    MIN ( Dates_DAX[Date] );
    FILTER ( Dates_DAX; Dates_DAX[ID] = EARLIER ( Dates_DAX[ID] ) )
)

M Language:

• On query editor select group by
  • Group by ID
  • Operation Min - Date
  • Operation - All Rows

• Expande table values

See below result for both columns and attach PBIX file.

Regards,

MFelix