Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

daxssastabular-model

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey.

I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date: 

Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]), 
Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY])))

This works well until you throw Requisition Status into the mix. I have rows for each requisition for every day in 2013. For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date. When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved.
Pivot Table

Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table.

The Requisition Status table looks like this:
Requisition Status

Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job. Sometimes it is 1, sometimes is is more than 1. It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.

Best Answer

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.

Related Question