Getting help with this complicated DAX question

Hey YoungLearning ,

 

please provide the pbix that you use, this enables us to be on the same page.

 

"Sum of Headcount Count" doesn't look like a measure is used.

 

Regards,

Tom

Next what is the initial question?
I think AlexisOlson already answered the question.

 

Due to the nature of headcount (it's called a semi-additive measure) and the type of the fact table. Because employees are counted every month this fact table has to be considered a snapshot fact table.

 

Assuming that an employee enters on january, this employee will be counted three times (jan, feb, and mar). But this doesn't mean that there are 3 employees at the end of Q1.

 

The measure

 

cnt of employees =
CALCULATE(
COuNTROWS('employeetable'),
LASTDATE('calendar'[date])
)

 

only counts the rows for the after the filter modifier of the CALCULATE are applied.

This means the headcount is filtered down to the 31st of March. Using LASTDATE prevents double counting for semiadditive measures like Headcount.

 

This article explains some of the intircate workings of LASTDATE: https://dax.guide/lastdate/

This article by Jeffrey Wang, one of the inventors of DAX explains what makes dates special: http://mdxdax.blogspot.com/2011/01/dax-time-intelligence-functions.html

 

Regards,

Tom