Need help in writing DAX to calculate HR - Productivity Saving

[adeeln]

Hi Everyone,
Need your help in writing DAX,
I'm using Direct Query Mode, this is nessary for project. For sample test attached Power BI is in Import mode.
I'm facing issue in order to calculate Productivity Saving (given below formula) for each employees using measures. Expected output is attached in SQL query form. That i want to acheive in
Power BI.

Could not getting why DAX returning wrong sum output for Productity Savings.
so scenario is
ProdGain | HourlyRate | Savings
10 x 20 = 200
15 x 10 = 150
------------------------------------
ExpectedResult for ProdSaving= 200 + 150 = 350

DAX calculating correct results on table row wise but wrong at total and card.
DAX help will be appreciated.

Formulas:

HC = total no of active employees before selected period
Hires = total no of employees hired on selected period
Termination = total no of employees terminated in selected period

Productivity Lose = YTD SUM (Leave[Hours]) -> for all HC employees

Productivity Gain = sum(Total X-Allocated Leaves * 8 ) - Productivity Loss

Productivity Index = 1 - (Productivity Loss (for selected Period) / (Total HC For upto selected Period * AverageMonthlyWorkHours))

Productity Savings = Productity Gain * (current Annual salary/Average Yearly Working Hours (calculated at the Period Level)

Attachments:
Power BI File (PBIX)

Power BI output

Expected Output row wise from SQL

Expected Output sum from SQL

Thanks

[edhans]

I'm having a little trouble following the formulas because it bounces between calculated columns and measures, but I don't understand what your MAXDATE is doing. Your formula total is wrong because it seems to be adding up all of the hourly rates and multiplying that by the hours (gross oversimplification) because at the total row, there is no filter context on what the rate is. It is not iterating through the hours.

This measure, on the otherhand, does.

Measure =
VAR MaxDate = MAX(DimDate[Date])
VAR Total =
SUMX(
    FILTER(
        HR,
        RELATED(DimDate[Date]) <= MaxDate
    ),
    [Productivity Gain(hrs)] * (HR[IndividualHourlyRate] - [terminated IndividualHourlyRate Measure])
)
RETURN
    Total

However, so does this one, and It ignores your maxdate logic.

Measure 2 = 
VAR Total =
SUMX(
    HR,
    [Productivity Gain(hrs)] * (HR[IndividualHourlyRate] - [terminated IndividualHourlyRate Measure])
)
RETURN
    Total

Again, I am 100% certian I am not following your logic all the way through, but if you can tell me what the maxdate is needed for, I might be able to help further. The max date in your date table is Dec 2020, so why aren't you just using ALL() if you need to remove the filters, or just let the date slicers do their work?

[adeeln]

Thanks @edhans 
I really appriciate your help, actually i dont know the purpose of MAX date.

I'v experience with Excel formulas so i'm little aware of DAX context.

I was just picked measures from internet. You can remove mine and apply your logic to get exact statistics.

Thanks.