dynamic date filter

[jasgun01]

Hi,

I have a Fact table "Reporting AllPolicies(Yellow Table) and I want to filter that base on that based on the users selection on the slicer "Accounting Date (Green Drop Down).

I have added a measure on the Yellow table to get the selected value from the slicer ("_SelectedDate")

Then I have added a new column and thought a basic if statement would allow me to filter the results.

See formula "_InForce" = 

if('Reporting AllPolicies'[PolicyInceptionDate] <= [_SelectedDate],"Y","N")

However, see highlighted red items that I would expect the results to be "Y".

Can someone assist on what I'm doing wrong or if there's another way to do it?

Thanks

Jason

[Mariusz]

HI @jasgun01 

You need to use Measure instad of a Column

Mariusz

[jasgun01]

Thanks for responding @Mariusz 

I thought I would try a measure, but as part of the if statement I couldn't choose the field "PolicyInceptionDate"

[Mariusz · Accepted solution]

Hi @jasgun01 

Try MAX(PolicyInceptionDate) it should give a the right result, measure will need some sort of aggregation as it does not have a row context.

Hope this helps 

Mariusz