Getting help with this complicated DAX question

[YoungLearning]

Can someone explain to me why CountRows since the employee name will be recorded for every month he/she stays employed, and why that filter LastDate Date?

[TomMartens]

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

[YoungLearning]

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)

Headcount Count = CALCULATE(COUNTROWS(Headcount), LASTDATE(Date_X[Date]))
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?

[TomMartens]

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

[AlexisOlson · Accepted solution]

The LASTDATE filter will adjust the filter context to only return a single date. COUNTROWS then calculates the employee count for that date (month).

[YoungLearning]

None of this though, seems to specify about returning the lastdate value in a specified month. How does this DAX function work for this use case? Thanks for your reply...

[AlexisOlson]

LASTDATE returns the latest date within whatever filter context is set, so if there is a slicer or a filter specifying a month, you get the last date in that month.

[YoungLearning]

I have tested it out and I kinda see how it works, on a visual level. But does DAX know that the last date of the month is e.g. 31st of December? How does using different date columns of different table affect the count, how does it know without using like Distinct Count or something?

[AlexisOlson]

It's referencing a date table which should have all dates you need, so it's just a max over the dates in the date context.

[YoungLearning]

fyi when i did this just to test it out... why is it like this?

MeasureLast = LASTDATE(Date_X[Date])

[TomMartens]

Hey @YoungLearning ,

as @AlexisOlson mentioned LASTDATE returns the last date of the filter context. The date hierarchy present in the table view is "contributing" to the filter context of the cell that shows the result of your LASTDATE measure. Each cell has a different filter context, for this reason the measure returns a different value in each cell. If you remove the day column from the table you will see that LASTDATE will return the last date of the month.

Regards,

Tom 

[YoungLearning]

Yes, seems like it. But back to the original question now. Why not the Headcount[date] column, why Date[date]? This columns have a relationship.

[YoungLearning]

It wants to count the number of employees, year, month and quarter basis. COUNTROWS definitely double count if I do it by year or quarter.

[YoungLearning]

example data