Can I create filter context derived from value of different filter context variable?

[watridge]

I'm pretty new to Powerpivot and DAX and seem to have got myself stuck. Hoping that the experts can give me a simple fix!

I have a fact table which has two date fields TransactionDate and CustomerStartDate and transaction amounts.
TransactionDate is related to the DimDate table (I use DimDate[Calendar Year])
CustomerStartDate is related to the StDimDate table. (I use StDimDate[SY.Calendar Year])

With this info I can very easily create a simple "cohort analysis" using StDimDate[SY.Calendar Year] as the row headers and DimDate[Calendar Year] for the col headers.

I'm also doing some more detailed Year over Year change calculations and I end up using SUMX to aggregate over customer ID's and sum up only the negative aggregated per customer ID results per YoY change == Downgrades and the Positive aggregated results per customer ID ==Upgrades.

Now comes the part where I am stuck. I want to aggregate only the Upgrades that are from cohorts that are more than 2 years older than "this" year:
Effectively I want to aggregate only when StDimDate[SY.Calendar Year]
However I can't find a way to do the calculation in powerpivot without error messages.

This formula gives me the correct result for the "this year = 2013 =DimDate[Calendar Year]" when I manually enter the value into the calculation:
=SUMX(ALL(combined[tblCustomer#CustomerID]),
CALCULATE(if(AND([DeltaSalesYoY]>0, [test1]>6),[DeltaSalesYoY],0),FILTER(StDimDate,StDimDate[SY.Calendar Year]<2013-1))
)

I'd like to have the calculation work for whatever value is the current context of the DimDate[Calendar Year] when I substitute it in for 2013 in the calculation above -- like this:
Replace: "2013-1"
With: "DimData[Calendar Year]-1"<dimdate[calendar year]-1))
<dimdate[calendar year]-1="" )
<dimdate[calendar year]-1="" )<dimdate[calendar="" )
<dimdate[calendar year]-1))
<dimdate[calendar year]-1)))



However I get the following error message:
"The value for column 'Calendar Year' in table 'DimDate' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified."

I think I must be missing a simple fix (I hope!)

Thanks for your help.

Regards
Pete</dimdate[calendar></dimdate[calendar></dimdate[calendar></dimdate[calendar></dimdate[calendar>

 

[watridge]

I found a way to get it to work. I needed to wrap the formula with something that guarantees that DimData[Calendar Year] only has a single value. So this now works:

=if(MAX(DimDate[Calendar Year])=MIN(DimDate[Calendar Year]),SUMX(ALL(combined[tblCustomer#CustomerID]),
CALCULATE(if(AND([DeltaSalesYoY]>0, [test1]>6),[DeltaSalesYoY],0),FILTER(StDimDate,StDimDate[SY.Calendar Year]<Max(DimDate[Calendar Year])-1))
),0)

Wondering if there is a way to do this more efficiently?

Thanks
Pete

 

[watridge]

Actually -- I over thought the issue -- this simpler verison works fine:

=SUMX(ALL(combined[tblCustomer#CustomerID]),
CALCULATE(if(AND([DeltaSalesYoY]>0, [test1]>6),[DeltaSalesYoY],0),FILTER(StDimDate,StDimDate[SY.Calendar Year]<Max(DimDate[Calendar Year])-1))
)

 

[scottsen]

I didn't actually read your post since found your own answer (go you!) but the IF (Max() = MIN()) ... you would typically do IF (HASONEVALUE())

 

[watridge]

Thanks Scott -- that is good function to know!