Add the sum of one measure from the beginning of time to a specific date to the sum of another measure from the specific date to the filter context?

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.

September 10th, 2015 4:05am

This sounds like a job for disconnected tables in your model.

This is a common technique in Power Pivot and you will usually see if referred to as Disconnected Tables, Disconnected Slicers, or Parameter Tables in various blogs and books.

It is a way to allow slicer selections to be used as a means of creating parameters for other measures.

Basically, you just add a table to your data model that contains the parameter values you want available in your slicer. The table should not be related to any other table in your model.  In your case it sounds like this table would contain some form of month and year values (Jan 2015, 2015 - 01, 201501, etc.)

Then you create a measure that essentially "harvests" the slicer selection.  This measure can then be used in other measures for filtering criteria as appropriate.

Here are some links that detail the use of this technique.  Your scenario is different but the basic idea remains the same.  This is a very adaptable and powerful technique that can add a lot additional interactivity to your pivots/charts.

http://www.powerpivotpro.com/2011/10/user-friendly-report-sorting-with-slicers/

http://www.daxpatterns.com/parameter-table/

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 5:46pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics