Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter a column dynamically using a measure

There is a column with FY i.e. "FY19","FY18","FY17" etc.

 

I Created a measure which calculates the current FY:

 

CurrFY = CONCATENATE("FY",IF(MONTH(TODAY()) <=3, VALUE(FORMAT(TODAY(),"YY")), VALUE(FORMAT(TODAY(),"YY")) +1))

e.g. output: "FY19"

 

 

Now i need to filter the report based on the FY column using the current FY i get from the CurrFY measure.

How do i do it?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi v-cherch-msft, Your solution somehow didn't work as a measure but works fine as a calculated column.

     

    Created the calculated column as below:

    FYFilter = IF(pipeline_summary_log_copy[FY] = [CurrFY], 1, 0)

    Then i added it to the report level filter.

     

    Thanks

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    You may create a measure as below and use it in visual level filter.

    Measure = IF(MAX(Table1[FY])=[CurrFY],1)

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-cherch-msft, Your solution somehow didn't work as a measure but works fine as a calculated column.

       

      Created the calculated column as below:

      FYFilter = IF(pipeline_summary_log_copy[FY] = [CurrFY], 1, 0)

      Then i added it to the report level filter.

       

      Thanks

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    7 years ago

    Anonymous  could you show an example with the data of what you expect, hard to visualise  what you saying.