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 the formula should be
formula = SUM(Column 1) where period = Q12020 / SUM(Column 1) where period = Q12019

 

Please help me !!

Need Help

13 Replies

  • MFelix's avatar
    MFelix
    Super 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
        Super 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 ".

         

         

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    6 years ago
    that sounds a little confusing please share some dummy data with the result you are expecting.
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , in case you have date , you can use time intelligence with date calendar

    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

     

    In case you do not have date - you can use qtr Rank // here date can be qtr table. But have it separate

    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)
    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
    Last year Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=(max('Date'[Qtr Rank]) -4)))

     

    Power BI — QTD
    https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.