Calculate a PayPeriod from a table with one Pay Date field

[drinko]

PBI beginner here, trying to learn DAX.  I have ADP_Labor[Timecard Pay Date] and I am trying to calculate a PayPeriod.  The logic is that the [Timecard Pay Date] is between two dates that define the period.  I created a dimension table that had Pay_Period[Period start date],Pay_Period[Period end date],Pay_Period[Pay Period].  I didn't know how to join that table to ADP_Labor.  I tried two joins, but only one could be active (needed some kind of implicit conditional join and I have no idea what I'm talking about).  The other option was to create a New Column in ADP_Labor called PayPeriod, but I have no idea how to write the DAX for that (tried many things).  The ADP_Labor table would have data for 2019-2021 and there are 26 pay periods per year (14 days per period).  Period 1 for each year states at a weird date for each year, but the math and logic would work out if I hard coded the start date.  2019 period 1 start date is 12/21/2018 and 2020 period 1 start date is 12/20/2019 and 2021 period 1 start date is 12/18/2020.  Grateful for any help.  

[v-eqin-msft]

Hi @drinko ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @drinko ,

According to my understanding, you want to add the Date period in Pay table to ADP table, right?

You could use the following formula:

Column =
VAR _date = [Timecard Pay Date]
RETURN
    CALCULATE (
        MAX ( 'Pay_Period'[Pay Period] ),
        FILTER (
            'Pay_Period',
            'Pay_Period'[Period start date] <= _date
                && 'Pay_Period'[Period end date] >= _date
        )
    )

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

[amitchandak]

@drinko , You have to join them in a measure

example https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[drinko]

Thank you, but now I don't know how to calculate Pay Period into a Date table that spans multiple years.  2019 period 1 start date is 12/21/2018 and 2020 period 1 start date is 12/20/2019 and 2021 period 1 start date is 12/18/2020.  Not sure which function to use.  

I thought there would be an easier approach to his without having to create a dimension table with hundreds of rows for each date.  I was hoping there was a DAX expression that could look at the ADP_Labor[Timecard Pay Date] and determine which Pay Period it is in without having to join to a Date table.  I'll try anything at this point though.

[amitchandak]

@drinko ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[drinko]

Yes Sir.  I can share some sample data.   FIrst time user so not sure where to click to share CSV files?

[amitchandak]

@drinko , One is you can copy paste from excel to here/ Or Upload on onedrive or dropbox and share the link

[drinko]

https://drive.google.com/drive/folders/1QXrhe8NphrGMcXpxEXyXz_ETLDWelpyz?usp=sharing 

Thank you for taking a look.

[drinko · Accepted solution]

Need a little more help.  I made some progress creating a calculated column in the ADP_Labor table.  I am using datediff to put each ADP_Labor[Timecard Pay Date] row into a Pay Period.  There are 26 pay periods per year.   I just need help figuring out how to put period 27 as period 1 (first one in a new year).  Any ideas?

pay period = FLOOR(DATEDIFF(DATE(2018,12,21),ADP_Labor[Timecard Pay Date],DAY)/14,1)+1