Dynamic Filter of Same Column

[Anonymous]

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 !!

[amitchandak]

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

[vanessafvg]

that sounds a little confusing please share some dummy data with the result you are expecting.

[MFelix]

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]

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]

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]

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

[v-lili6-msft]

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

[MFelix]

Can you share the formula you are using please.

On the test I have made everything was working properly. 

[Anonymous]

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 · Accepted solution]

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]

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?

[MFelix]

Hi @Anonymous ,

The fact that yuou use a calculated column should not be an issue, and based on the information you are giving the calculation should be correct, can you share the MRR values so I can check it out. As describe in my previous answers and on the example given by @v-lili6-msft  the formula is working however this can be a problem with your model and canb e related with data format, filtering or some other context that can influence your measure.

[v-lili6-msft]

hi  @Anonymous 

I don't think this is problem that cause your error, if possible, share a simple sample pbix file, that will be a great help.

Regards,

Lin