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 ,

You need to create a measure similar to the one below:

 

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

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MFelix, Thanks for the suggestion, but it is not working. Let me reiterate the problem. Suppose you have a column with Qtr & Year value like Q1 2020, Q2 2020 and so on. So if I select Q1 2020, I should get the sum of other column lets call it 'A' of the period Q1 2019 which is same quarter but 1 year less. Somehow the code you hae shared is not working.

 

Thanks

  • 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.