Filtering table by variable only

[rode]

Hi everyone!

I have a table called Calendar and it contains some fields as Date, Year, Month, etc.

The second table (the fact one) is related to the previous one by the Year field.

I would to do the following computation:

Back Margin Variance CAGR = 
CALCULATE(
	[Back Margin];
	FILTER('Calendar'; 'Calendar'[Year] = LASTNONBLANK('Calendar'[Year]; TRUE()))
	)

But it doesn't work as expected because it does no apply the filter on Calendar table. Instead, using the following formula, it performs the expected computation:

Back Margin Variance CAGR = 
VAR
	LY = LASTNONBLANK('Calendar'[Year]; TRUE())
RETURN
CALCULATE(
	[Back Margin];
	FILTER('Calendar'; 'Calendar'[Year] = LY)
	)

Could anyone explain why this happens, please?

Thanks.

[austinsense]

Without knowing everything, here's what I think is going on at a very high level.

When you use the variable it calculates LY in the right context. With FILTER() it calculates LY in the wrong context.

With FILTER() you're calculating LASTNONBLANK for every row of the calendar table, and for each row in the calendar table the LASTNONBLANK is the date in that row.  So for every row in the calendar table this expression is true - Calendar'[Year] = LASTNONBLANK('Calendar'[Year]) - and so you get every row back with no filtering.

[rode]

---

@austinsense wrote:

Without knowing everything, here's what I think is going on at a very high level.

When you use the variable it calculates LY in the right context. With FILTER() it calculates LY in the wrong context.

With FILTER() you're calculating LASTNONBLANK for every row of the calendar table, and for each row in the calendar table the LASTNONBLANK is the date in that row.  So for every row in the calendar table this expression is true - Calendar'[Year] = LASTNONBLANK('Calendar'[Year]) - and so you get every row back with no filtering.

---

Hi @austinsense and thank you for your reply.

I was thinking the same thing. But I'm so curious to know which is the detail which I'm not getting in touch with to explain the reason of this strange behaviour.

Thank you.

[v-yulgu-msft]

Hi @rode,

Would you please share some sample data for further analysis? I need to know the source data in Calendar and Fact table, and the formula of measure [Back Margin] so that I can reproduce your scenario. 

Thanks,
Yuliana Gu

[rode]

Hi @v-yulgu-msft,

here it is the sample of Calendar:

The formula of back margin is the next one:

Back Margin = 
	CALCULATE(
		SUM(TMM[KPI value]) / 1000;
		TMM[KPI name]="Back Margin Promo" || TMM[KPI name]="Back Margin NoPromo"
	)

where TMM table has some columns, but the interested ones are KPI name (the ones with the names reported above) and KPI value.

Thank you so much!

Regards.