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

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

Hi Anonymous ,

 

Is the columns where you are filtering "Q1 2020" or "Q12020"

If you have a space in the middle you need to change the formula a little bit try this one:

 

Vaddlue =
VAR Year_Selected =
    RIGHT ( SELECTEDVALUE ( 'Table'[Quarter] ), 4 )
VAR Quarter_Selected =
    LEFT ( SELECTEDVALUE ( 'Table'[Quarter] ), 3 )
RETURN
    SUM ( 'Table'[Value] )
        / CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Quarter] ),
                'Table'[Quarter] = Quarter_Selected & ( Year_Selected - 1 )
            )
        )

Using the value 3 on the LEFT formula you will pick up the "Q1 ".

 

 

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

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

    HI  Anonymous 

    Getting infinity value means there is no value for last year for same quarter by this formula,

    MFelix's formula should work, Please check if data is right, for example, filter Q12020 and Q12019 together to see if there is data in Q12019.

    If you still have the problem, please share a simple sample pbix file, that will be a great help.

     

    Regards,

    Lin