PivotTable Filter Context in Calculated Column

[TCPaul]

Hi guys,

I have the following problem; I am trying to create a calculated column that shall later be used as filter in a pivot table which shows if an employee was employed during the selected time filter (e.g. 2016). As a measure this looks works perfectly and looks like:

CurrEmpl:=CALCULATE([Count of Sales Reps],filter(dPeople,MIN(dDates[Date])<=dPeople[End Date]&&max(dDates[Date])>=dPeople[Start Date]))

However, since I can't filter for the result in a pivot table, I have to somehow replicate it as a calculated column, which is not working for me so far; here is my current attempt which is basically the same as the measure:

=calculate(COUNTROWS(dPeople),filter(dPeople,MIN(dDates[Date])<=dPeople[End Date]&&max(dDates[Date])>=dPeople[Start Date]))

This ends up returning 1s all the way, ignoring the date filter that is on the output pivot table.

Any ideas? Thanks ahead!

 

[Matt Allington]

You have to choose between a measure that will react to your slicers and filters on pivots, or a calc column that does not. So if you want a calc column, the only option really is to do the calc at a point in time - eg today. So you could do a calc column in the people table that compares today's date with their start and end dates to see if they are currently employed (yes or no), but then you can't change the period.

If if you put your current measure in values in a pivot, and employee on rows, I assume it will give you a list of current employees for the current date filter context. So what other filtering do you want? Maybe there is another way but it depends what you want to do.