Forum Discussion
Getting help with this complicated DAX question
The LASTDATE filter will adjust the filter context to only return a single date. COUNTROWS then calculates the employee count for that date (month).
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
I am not allowed to attach any files, but perhaps i can paste some snapshots.
First, the original excel files imported into PBI desktop:
1) Calculated column Headcount Count and measure last date[date] (given answer)
last date[date] = LASTDATE(DATE[DATE])
2 ) Measure Headcount_Count_new and measure last date [date]
3) Calculated column Headcount Count and measure last headcount [date]
Headcount Count = CALCULATE(COUNTROWS(Headcount), LASTDATE(Headcount[Date]))
last headcount [date] = LASTDATE(Headcount[DATE])
4) Measure Headcount_Count_new and measure last headcount [date]
After using slicer to select, say quarter 4 it basically shows the same table visuals.
What would you do?