Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lucjan_Szulik
Regular Visitor

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 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.

 

Capture.JPG

 

Thanks,

Lucjan

 

1 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

group.png

  • Expande table values

expand.png

 

See below result for both columns and attach PBIX file.

 

Untitled.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi @Lucjan_Szulik,

 


Can you mark the proper answer as a solution please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

group.png

  • Expande table values

expand.png

 

See below result for both columns and attach PBIX file.

 

Untitled.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.