We have a datawarehouse solution set up with Power BI and SSAS Tabular which, among other things, include a fact table for our current employee record (HR). This fact table is extracted from the HR system once per month with one row for every employee in our organization. Columns include Employee number, their percentage of employment and a date ID for when the data was extracted from our HR system.
For the sake of this example, assume that we have two dimension tables: A full calendar table (Time) and an employee dimension (Employee) in a star schema.
An important indicator for us is the number of full-time employees we have (FTEs), calculated as the sum of all the employment percentages. However, this measure also needs to account for the fact that the employee record table is a snapshot and thus cannot be summed across dates. For situations where the end users select only a year as a filter from the date dimension, we've decided that the FTE measure should show the sum of employment percentages for the last date in the fact table for that year. Generally speaking, the FTE measure should always show the sum of employment percentages for the last date within the selected time period.
I've come up with the following DAX formula, which seems to do the job:
Sum of FTEs:=VAR SelectedDate=LASTNONBLANK(Time[Date],SUM(HR[EmploymentPercentage]) RETURN CALCULATE(SUM(HR[EmploymentPercentage]),Time[Date]=SelectedDate)
However, this measure produces unexpected results when I create a pivot table with employee names from the Employee table in the row section. Employees which are not a part of the latest snapshot are shown with their corresponding measure value. They are, however, not included in the grand totals. I assume this is because the measure is evaluated for each row, and since each row is represented by an employee, the SelectedDate variable equates to the date for which the employee last existed in the fact table.
Below is a screenshot of a pivot table illustrating the problem:
Our last snapshot was October 2017, and as no year has been selected in the filter, the measure should only include data from the HR snapshot for October 2017. You can see that the grand total of 5.6 is correct, but Employees 1, 5, 7, 8 and 10 (marked red) are still shown even though they are no longer employed.
Ideally, the pivot table should look like the one below:
Onto my question (finally): How I can create a DAX measure that also dynamically filters the date dimension properly so that irrelevant employees are excluded from the pivot table? Ideally, this would be a filter which finds the last date ID in the fact table within the current filter context of the date dimension. I've tried to use variables to set Year and MonthNumber using HASONEVALUE, but as my date dimension also includes fields like Quarter and Semester, this has proven too difficult.
Any thoughts or ideas would be greatly appreciated!
Please try the following formula and check if it works fine.
Sum of FTEs := VAR SelectedDate = LASTNONBLANK ( Time[Date], 1 ) RETURN CALCULATE ( SUM ( HR[EmploymentPercentage] ), Time[Date] = SelectedDate )
Thank you so much for the reply.
Unfortunately that doesn't do the trick. The SelectedDate variable just returns the last date from the date dimension, without checking if there is data for that date in the fact table. For instance, if I select Year=2017, SelectedDate is set to 2017-12-31 while the last data in my fact table is 2017-10-10. So the measure returns a blank.
Any other suggestions?
Do you mind share your sample table or .pbix file for further analysis? You can send it by private message if your data is confidential.