I am trying to set up a measure that will calculate the number of rows in a table. The tricky part is that I am using multiple slicers, one of which is a 'as of date' that each row of my table contains. I want the measure to be able to count the number of rows by using the date selected in the slicer but reporting back the count from a different date. To be more specific, my table is a list of all employees month over month - so each active employee will have a row per month they are here. The slicer would allow the user to select a month that they wish to see the data from. The measure I am trying to create is the count of all rows for the January month data in whichever year the slicer 'as of date' was selected. So if I am looking at October 2022 data (selected Oct 22 in the slicer), my measure should give me the count from January 2022. I have been able to set it up using the formula below but it won't recognize selections of my other slicers, of which can have more than one item selected in each. Any ideas??
Beg Year HC =
VAR _1 =
'HC Walk Table'[Selected_Date]
RETURN
CALCULATE (
COUNTROWS('Monthly EE Census Data'),
FILTER (
ALL('Monthly EE Census Data'),
'Monthly EE Census Data'[As of Date] = _1
)
)
where Selected_Date = DATE(YEAR(SELECTEDVALUE('Calendar'[MonthYearLong])), 1, 1)
