Forum Discussion

Lucjan_Szulik's avatar
Lucjan_Szulik
Regular Visitor
8 years ago
Solved

Earliest date for employee - Power Query Editor

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...
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    8 years ago

    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

     

Lucjan_Szulik's avatar
Lucjan_Szulik
Regular Visitor
8 years ago

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's avatar
MFelix
Super User
8 years ago

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