Lookup shift date in a payroll period date range

[Malsk1_1]

Hi All,

I have 2 tables:

Payroll Shifts & Payroll Schedule.

Payroll Schedule has the following data points:

Payroll Period	Payroll Period Name	Start Date	End Date
31/01/2023	Jan 2023	27/12/2022	23/01/2023
28/02/2023	Feb 2023	24/01/2023	20/02/2023
30/03/2023	Mar 2023	21/02/2023	27/03/2023

The start and end date represent the shift dates within the payroll period.

Payroll Shifts has the following data attributes:

Employee Name	Employee ID	Shift Date
John	123	28/12/2022
Jane	432	24/01/2023
Fred	987	29/12/2022

The outcome I want on the Payroll Shift table is as follows:

Employee Name	Employee ID	Shift Date	Payroll Period Name	Payroll Period
John	123	28/12/2022	Jan 2023	31/01/2023
Jane	432	24/01/2023	Feb 2023	28/03/2023
Fred	987	29/12/2022	Jan 2023	31/01/2023

And so on - the above is just the core example - how can i do this without any natural join between the 2 tables?

I then need that Payroll period as a date slicer for the report.

I would prefer it in M if possible..

Thanks in advance.

[Malsk1_1]

@Ashish_Mathur thank you for your input - on this occasion i went with the M-Query although it is slow performance.

[Ashish_Mathur]

You are welcome.

[Malsk1_1]

@v-yueyunzh-msft thank you for the M-Query - it has worked, but it is quite slow - when i refresh the data it goes row by row and can take up to 5 mins to refresh 180 rows - this data is only going to grow and may eventually become problematic - can this be tuned?  All data is in excels that are on Sharepoint Online

[v-yueyunzh-msft]

Hi, @Malsk1_1 

According to your description, this new column is very slow, but it is not complicated for this creation logic, that is, each row is searched row by row on Table 1, and the conditions are met and returned.And according to your needs, you need to iterate through all the rows in Table 1 to get the value.

You can try publishing PBIX to Service for refresh and see if it takes a long time.
If so, you may need to use measures to calculate these two columns.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

These calculated column formulas works

Column = CALCULATE(MAX('Payroll schedule'[Payroll Period Name]),FILTER('Payroll schedule','Payroll schedule'[Start Date]<=EARLIER('Payroll shift'[Shift Date])&&'Payroll schedule'[End Date]>=EARLIER('Payroll shift'[Shift Date])))

Column 2 = CALCULATE(MAX('Payroll schedule'[Payroll Period]),FILTER('Payroll schedule','Payroll schedule'[Start Date]<=EARLIER('Payroll shift'[Shift Date])&&'Payroll schedule'[End Date]>=EARLIER('Payroll shift'[Shift Date])))

Hope this helps.

[v-yueyunzh-msft · Accepted solution]

Hi , @Malsk1_1 

Here are the steps you can refer to :
(1)We can click "Custom Column" in Power Query Editor:

(2)We put this two in two custom columns.

try Table.SelectRows(#"Payroll Schedule",(y)=> 
y[Start Date]<=[Shift Date] and y[End Date]>=[Shift Date])[Payroll Period Name]{0} otherwise null

try Table.SelectRows(#"Payroll Schedule",(y)=> 
y[Start Date]<=[Shift Date] and y[End Date]>=[Shift Date])[Payroll Period]{0} otherwise null

(3)Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly