I want to do something that feels a little complicated.
We have a measure that calculates how many employees have started from the beginning of time until the Pivot table filter context:
Employees started:=CALCULATE(COUNTA(tEmployees[Employed from]);FILTER(ALL(tDate[Date]);tDate[Date]<=MAX(tDate[date])))
We also have another measure that calculates how many employees have quit.
Employees quit:=CALCULATE(COUNTA(tEmployees[Employed until]);FILTER(ALL(tDate[Date]);tDate[Date]<=MAX(tDate[date])))
We have another measure that calculates have many employees are forecasted to start from the beginning of time until the Pivot table filter context:
Forecasted to start:=CALCULATE(COUNTA(tForecast[Employed from]);FILTER(ALL(tDate[Date]);tDate[Date]<=MAX(tDate[date])))
All of the measures above work.
Now we want to create a new pivot table report that uses all of those measures and adds some complexity. I will explain it below.
1) The user should select a month (and year) as current month
2) A series in the pivot table should show employees started, but it should not include any employees who start in or after current month
3) A series in the pivot table should show employees quit. This series is not affected by the selection of current month
4) A series in the pivot table should show employees forecasted to start. It should not include any forecasts that are for events prior to current month.
How can I create a new filter where the users can choose current month (1) and how can I create measures (2 and 4) where this current month is used in the way described?
I would be really happy I were able to find a solution to this report. I appreciate any help immensely.