HOW to write DAX to filter measures using slicer date

Question

I have date slices with Begining and End date and I need to count the total number of employees at the beginning of the slicer date and total number of employees at the end of slicer date. Please help me in writing the DAX

Active employee = COUNTROWS('Turnover Active') need to get the number at the beginning of the slicer date and End Date

Thank you

Could you share some example data in the same format as your data table and expected outputs? — BarneyL
I need number 200 active employee at the beginning of the period (slice beginning date and 123 at the end of the sliced date — Bihon Noro
What is the data structure? Do you have a calendar Dimension, is the data all in one table? please show an example — Jon
I have a calendar date table where I used the slicer and one staff table where the main data stored. I need to filter by employe status column and slicer date Example slicer date is 06/01/2018 and 06/30/2019 I need Active employee by 06/01/2018 and active employee by 06/30/2019 — Bihon Noro

Strawberryshrub Strawberryshrub · Accepted Answer · 2019-11-06T08:00:33

Add a new table to your model with possible date values. For example:

TableName: DateTable
TableColumnName: DateValue (for example with values from 01.01.2019 to 06.11.2019)

Now add a slicer to your report and drag and drop the column DateValue into it.

With the following two measures you will get the start and the end date from this slicer:

MinDate = Min(DateTable[DateValue])
MaxDate = Max(DateTable[DateValue])

Now add a measure to calculate your total numbers of employees:

MinDateEmployees = Countrows(Filter(EmployeeTable; EmployeeTable[EmployeeDate] < MinDate))
MaxDateEmployees = Countrows(Filter(EmployeeTable; EmployeeTable[EmployeeDate] < MaxDate))

HOW to write DAX to filter measures using slicer date

1 Answers