Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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".
 
 
 
Can someone assist on what I'm doing wrong or if there's another way to do it?
 
Thanks
 
Jason
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    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

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    HI Anonymous 

    You need to use Measure instad of a Column

    Mariusz

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

        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