Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Filter of Same Column

Hi,   I need to calculate a formula wherein if I select Q1 2020 it must get divided by Q1 2019 ( previous year).  The column has values like Q12020, Q22020, Q32020 and so on. Below is an idea how ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    This works on my side.

     

    and here is my sample pbix file, please try it.

     

    Could you please share your sample pbix file, there should be something wrong in other.

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for the prompt response but I have already taken care of the space. I am getting infinity value using this formula.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Can you share the formula you are using please.

 

On the test I have made everything was working properly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    VAR Year_Selected =
    RIGHT ( SELECTEDVALUE ( 'PowerBI'[Qtr_FY] ), 4 )
    VAR Quarter_Selected =
    LEFT ( SELECTEDVALUE ( 'PowerBI'[Qtr_FY] ), 3 )
    RETURN
    CALCULATE (
    SUM ( 'PowerBI'[MRR] )
    ,FILTER (
    ALL ( 'PowerBI'[Qtr_FY] ),
    'PowerBI'[Qtr_FY] = Quarter_Selected & ( Year_Selected-2)
    )
    )


    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      hi Anonymous 

      This works on my side.

       

      and here is my sample pbix file, please try it.

       

      Could you please share your sample pbix file, there should be something wrong in other.

       

      Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        One question - the same formula is not working in my PBI because the column Qtr_FY is a calculated one not just the imported column?