cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jasgun01 Frequent Visitor
Frequent Visitor

dynamic date filter

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".
 
PBI_Dates.png
 
 
Can someone assist on what I'm doing wrong or if there's another way to do it?
 
Thanks
 
Jason
1 ACCEPTED SOLUTION

Accepted Solutions
Mariusz Established Member
Established Member

Re: dynamic date filter

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

3 REPLIES 3
Mariusz Established Member
Established Member

Re: dynamic date filter

HI @jasgun01 

You need to use Measure instad of a Column

Mariusz

jasgun01 Frequent Visitor
Frequent Visitor

Re: dynamic date filter

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"

 

PBI_Dates2.png

Mariusz Established Member
Established Member

Re: dynamic date filter

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