Total Level not summing correctly when filtered by contract start date

DAX_Misteries — Mon, 30 Oct 2023 15:13:35 GMT

Hi guys,

I have a simple model with employee's that belong to certain departments and their timelogs.

The FirstContractDate is assigned to each employee.

I need to calculate the total number of hours registered in the system this year from contract start (if contract start is within the current year, ) or from beginning of year, if contract start is in previous years.

My measure is listed bellow. I get the right result per row but on the total level the result is not correct. It's because it takes into consideration the contract start date, instead of summing per each employee the values.

Var _StartOfContract =

IF( MAX(DimEmployee[FirstContractDate]) < MIN(DimCalendar[Date]),

MIN(DimCalendar[Date]),MAX(DimEmployee[FirstContractDate]) )

Return

CALCULATE(

SUM(FactTimeSheet[Hours]),

DATESBETWEEN(DimCalendar[Date], _StartOfContract,TODAY()-1 ))

I used Summarize and addcolumns to get the right result but even that produced the wrong totals:

Var _StartOfContract =

IF( MAX(DimEmployee[FirstContractDate]) < MIN(DimCalendar[Date]),

MIN(DimCalendar[Date]),MAX(DimEmployee[FirstContractDate]) )

Var _CalcTable =

ADDCOLUMNS(

SUMMARIZE(

DimEmployee,

DimEmployee[EmployeeID]), "HoursInYear",

CALCULATE(

SUM(FactTimeSheet[Hours]),

DATESBETWEEN(DimCalendar[Date], _StartOfContract,TODAY()-1 )))

Return

SUMX(

_CalcTable,

[HoursInYear])

Can you tell me what am I doing wrong, as I thought that SUMX would just itterate through the table that has the right results, but the contract date is proving to affect the result after all....
As you can easilly see the 1.554 + 1.252 + ... for sure don't sum up to 1.834 😄

Thank you for your help!

Re: Total Level not summing correctly when filtered by contract start date

some_bih — Tue, 31 Oct 2023 08:03:18 GMT

Hi DAX_Misteries "wrong" total could be resolved depending on your scenario. Still, to decrease writing and be efficient it is the best, if you can share model / file and write scenario when total is ok /wrong (expected output for some input)

Re: Total Level not summing correctly when filtered by contract start date

DAX_Misteries — Tue, 31 Oct 2023 09:09:17 GMT

Hi neighbor, thank you for trying to help out 🙂

I simplified the model and you can find it on this link:

https://drive.google.com/file/d/1yIJi2H6bmyGa7cFM1F74jei6qCZgDxhO/view?usp=drive_link

the measure is Hours Wrong Calc and you can see at first glance that it's not correct:

topic Total Level not summing correctly when filtered by contract start date in DAX Commands and Tips

Total Level not summing correctly when filtered by contract start date

Re: Total Level not summing correctly when filtered by contract start date

Re: Total Level not summing correctly when filtered by contract start date