cancel
Showing results for
Did you mean:
Regular Visitor

## Filtering table by variable only

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.

4 REPLIES 4
Community Support Team

## Re: Filtering table by variable only

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Filtering table by variable only

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.

Highlighted
Established Member

## Re: Filtering table by variable only

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.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast
Regular Visitor

## Re: Filtering table by variable only

@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.