Calculating forecasted employee Pay

[SAMV]

Hi,

I'm new in the community and need help figuring out a way to calculate the forecasted pay for a certain pay period.

So I've a table with employee details such as Name, rate, start date and end date.

2nd table with all dates, pay period start date for that day, pay period end date for that date and pay date.

I need to figure out a way to calculate the employee pay for every employee at every pay day.

So the logic should be,
- if the employee start date is before pay period start date, pay period start from pay period start date
 - if the employee start date is after pay period start date, pay period start from employee start date

-if employee end date is before pay period end date then pay period end would be employee end date
-if employee end date is after pay period end date then pay period end would be pay period end date

and from there, for each pay date, what would be the pay for each employee as per
                           the working days in that period x average hr per day x pay rate

Currently, there is no relationship between these to table.

Please help. Thanks

[SAMV]

Thanks for helping me out with this @johnt75. Really appreciate it! 
This is the DAX code I used.

Total Margin Forecast = SUMX (

    SUMMARIZE (

        'Date',

        'Date'[Pay Period Start Date],

        'Date'[Pay Period End Date],

        'Date'[Pay Date]

    ),

    SUMX (

        'Placement',

        VAR startDate = MAXX (

            { Placement[Contract Start Date], 'Date'[Pay Period Start Date] },

            [Value]

        )

        VAR endDate = MINX (

            { Placement[Contract End Date], 'Date'[Pay Period End Date] },

            [Value]

        )

        VAR numDays = CALCULATE (

            SUM ( 'Date'[Is working day] ),

            REMOVEFILTERS ( 'Date' ),

            DATESBETWEEN ( 'Date'[Date], startDate, endDate )

        )

        RETURN numDays * Placement[Avg hr per day] * Placement[Margin per hour]

    )

)

This gives the forecasted margin.

I've got a further question to this.
I want to show how the actuals + forecast looks like according to the date selected.
So I've got another table, This has 
Pay Date, margin
Now I want a DAX to provide me with
Actual margin for the completed months (as margin should be present) and forecast for rest
For example
Lets say we are forecasting for this Financial year July 23 to Jun 24
If I were looking at it on 28/09/2023,
I want the the new dax to show
July 23 and August 23 actuals margin + Forecast for Sep 23 to June 24

Could you please help me with this @johnt75 

[johnt75 · Accepted solution]

Add a new column to the date table like

Is Working Day = IF ( NOT WEEKDAY('Date'[Date]) IN { 1,7 }, 1, 0)

and then you could create a measure like 

Emp Pay =
SUMX (
    SUMMARIZE (
        'Date',
        'Date'[Pay Period Start Date],
        'Date'[Pay Period End Date],
        'Date'[Pay Date]
    ),
    SUMX (
        'Employees',
        VAR startDate =
            MAXX ( { 'Employees'[start date], 'Date'[Pay Period Start Date] }, [Value] )
        VAR endDate =
            MINX ( { 'Employees'[end date], 'Date'[Pay Period End Date] }, [Value] )
        VAR numDays =
            CALCULATE (
                SUM ( 'Date'[Is working day] ),
                REMOVEFILTERS ( 'Date' ),
                DATESBETWEEN ( 'Date'[Date], startDate, endDate )
            )
        RETURN
            numDays * 'Employees'[average hr per day] * 'Employees'[pay rate]
    )
)

[SAMV]

Thankyou!