Using a measure in a filter condition in PowerPivot

Question

My overall goal is the following: calculate the all-time distribution of attendance across all four fiscal quarters using only data from before the current fiscal year. I have a measure that does this accurately for the current filter context. What I can't seem to manage is figuring it out for previous quarters from the current context.

If I can calculate the all-time (pre-2015) attendance for the previous quarter, I can take it from there. I couldn't get the Date Intelligence functions to work because I had to unfilter, then refilter the year field. Here's my Quarterly attendance measure. I just can't figure out how to walk it back a quarter.

QTotal:=CALCULATE(SUM([CNF]),
 ALLEXCEPT(DateTable,DateTable[ACQuarter]),
 DateTable[ACYear]<2015)

The next thing I tried was specifying the quarter directly in the filter. The following measure reliably calculates the previous quarter number:

PrevQ:=MOD(LOOKUPVALUE(DateTable[ACQuarter],
 DateTable[DateKey],
 FIRSTDATE(DateTable[DateKey]))-2,4)+1

But when I sub it into the following measure...

Test:=CALCULATE(SUM([CNF]),
 FILTER(ALL(DateTable),
 DateTable[ACQuarter]=[PrevQ] && 
 DateTable[ACYear]<2015))

...all I get are blanks. The exact same measure with a number instead of the measure works, though. I guess it's calculating [PrevQ] in the inherited ALL context instead of the unaltered filter context.

What am I missing? Any help would be greatly appreciated.

Some context may help. Like table structure and relationships. Welcome to SO! — guitarthrower
The two tables in question are 'Events' and 'DateTable'. [start_date] in 'Events' links to [date_key] in 'DateTable'. Attendance is recorded in the events table in the [CNF] column. 'DateTable' has a row for every date since 1/1/2010. It includes many fields, but the relevant ones are [ACQuarter] and [ACYear], which record the fiscal quarter and year for that particular date. — volfied

volfied volfied · Accepted Answer · 2015-04-09T16:43:02

I figured out a way to do what I was attempting by creating a separate measure to calculate the all-time total of each quarter, then using a SWITCH function to pick between them.

Using a measure in a filter condition in PowerPivot

1 Answers