DAX Measures for Employee Count Based on Date Filtering

Overview

In this section, you'll learn how to create DAX measures for counting rows in the Output_EmployeeList_Original dataset based on the specified date conditions. The goal is to plot these counts on a line chart, with months from date_table_output as the X-axis.

Measures Required

1. Last Date of the Month Measure

This measure retrieves the last date of each month to ensure that only the latest entries are counted for previous months.

LastDateOfMonth = 
EOMONTH(MAX(date_table_output[As of Date]), 0)

• Purpose: Get the last date of the current month for the context of the filter.
• Usage: This serves as a reference point for filtering previous month's data.

2. Filtered Employee Count Measure

This measure counts the rows in Output_EmployeeList_Original based on the date criteria.

CountEmployees = 
VAR SelectedDate = SELECTEDVALUE(date_table_output[As of Date])
VAR LastDate = [LastDateOfMonth]
RETURN
    IF(
        SelectedDate = BLANK(),
        CALCULATE(
            COUNTROWS(Output_EmployeeList_Original),
            FILTER(
                Output_EmployeeList_Original, 
                Output_EmployeeList_Original[As of Date] = LastDate
            )
        ),
        CALCULATE(
            COUNTROWS(Output_EmployeeList_Original),
            Output_EmployeeList_Original[As of Date] = SelectedDate
        )
    )

• Purpose: Count rows under specific conditions depending on the selected date.
• Logic:
  1. Retrieve selected date from the filter context.
  2. If there is no selected date, count rows for the last day of the month.
  3. If a date is selected, count rows for that specific date.

3. Month-Year Formatting Measure (For X-axis)

To ensure that the X-axis displays the months correctly in the line chart, you can create a measure to format the month and year.

MonthYear = 
FORMAT(date_table_output[As of Date], "MMMM YYYY")

• Purpose: Create a user-friendly month-year format for X-axis display.

Implementation Steps

1. Create Date Table: Ensure that date_table_output is a properly formed date table, connected to the Output_EmployeeList_Original data model via the As of Date.
2. Add Measures: Create the measures stated above in your Power BI environment.
3. Set Up Line Chart:
   • Place the MonthYear measure on the X-axis.
   • Use the CountEmployees measure for the Y-axis.

Example of Use Case

Assuming you've created the measures and the date table is set correctly:

• If you have a line chart showing the number of employees for each month and you select the date of "2023-03-15" in the filter, it will count the employees who were present on "2023-03-15".
• For any month before March 2023, it will show the count of employees for the last business day of that month.

Conclusion

These DAX measures will allow you to effectively visualize employee counts against a date context. This implementation follows best practices and offers robust and scalable solutions for date-based data analysis.

For further learning on DAX and advanced data modeling techniques, consider exploring the resources available on the Enterprise DNA Platform.