topic Remove Variable filter context in DAX Commands and Tips

Remove Variable filter context

MrMichael — Wed, 22 Jun 2022 09:56:19 GMT

Hi,

I am calculating earned premium up till a specific date using the variable "LatestReportingDate".
However, when using this variable, the filter context "YOA <= 2020" is kept.

Earned =

VAR LatestReportingDate =

CALCULATE(MAX(TABLE[ReportingPeriodStartDate]),
FILTER(TABLE, TABLE[YOA] <= 2020))

RETURN CALCULATE(SUM(TABLE[EarnedPremium]),
FILTER(TABLE, TABLE[ReportingPeriodStartDate] = LatestReportingDate)
)

When visualizing

Class	1	2
YOA	Earned	Earned
2019	xxx	xxx
2020	xxx	xxx
2021	BLANK	BLANK
2022	BLANK	BLANK

It seems the filter context from LatestReportingPeriod spills over such that earned is not calculated for 2021 and 2022 YOA.
I have tried using ALL, ALLEXCEPT to no avail. This also removes connections in the model, which I do not want.

Re: Remove Variable filter context

SpartaBI — Wed, 22 Jun 2022 11:01:23 GMT

MrMichael try this:

Earned = VAR LatestReportingDate = CALCULATE ( MAX ( TABLE[ReportingPeriodStartDate] ), TABLE[YOA] <= 2020 ) RETURN CALCULATE ( SUM ( TABLE[EarnedPremium] ), TABLE[ReportingPeriodStartDate] = LatestReportingDate )

Showcase Report – Contoso By SpartaBI

Re: Remove Variable filter context

tamerj1 — Wed, 22 Jun 2022 11:07:39 GMT

Hi MrMichael
The LatestReportingDate will return the last available date in in current filter context only if the year is 2020 or less. In the case of the year 2021 it will return blank. In this case the measure will also return blank as there is no blank date in your table.
You can FILTER ( ALL ( Table ), ..... in both measures but the best way to achieve what you need is the have a date table.

Re: Remove Variable filter context

MrMichael — Wed, 22 Jun 2022 11:21:28 GMT

Thank you.
However, I find that using ALL on both measures, removes connections in my model which makes earned premium the same for all years.

Re: Remove Variable filter context

tamerj1 — Wed, 22 Jun 2022 11:38:39 GMT

MrMichael
This is why I advised you to use a date table. This will give you the freedom to filter without the need for sophisticated dax code.