Need Help to Show values of previous Quarter based on selection

[Anonymous]

HI @PBCommunity 

I spend couple of hours to resolve this but didn't succeed.

I wanted to show the sum of values of prvious quarter based on slicer selection using selected value.

This is how i tried to implement.

I have created a measure to give the previous quarter number as per user selection

=> Previous Quater = SELECTEDVALUE(Sheet1[quater])-1

and then use this result in other measure to get some of values of the prevouious month

=> cal measure = CALCULATE( sum(Sheet1[amount]),FILTER(Sheet1,Sheet1[reason]="A" && Sheet1[quater]=[Previou Quater]))

This will work fine if i remove -1 from the previous quater and it shows the correct sum of selected value but when i put -1 as shown above for previous month i gives blank.

Here is the data set and output in both case without -1 and with -1.

output without -1

output with -1

Please help me to resolve this thanks.

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure 2 = 
VAR _q=max('Table'[quarter])
return CALCULATE([Measure],FILTER(all('Table'),'Table'[quarter]=_q-1))

pls see the attachment below

[sam_gift]

Hi @ryan_mayu , Please let me know how will you calculate Previous Year QTD the same way. Thanks

[Anonymous]

Thanks @ryan_mayu  for help.This solution works for me

[ryan_mayu]

you are welcome

[amitchandak]

@Anonymous , Try like

CALCULATE( sum(Sheet1[amount]),FILTER(all(Sheet1),Sheet1[reason]="A" && Sheet1[quater]=[Previou Quater]))

Better to have a separate Qtr/date Table

This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr]=max('Date'[Qtr])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr]=max('Date'[Qtr])-1))

The best is have a Rank on Year Qtr column

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense)  //column

Measure 
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

[Anonymous]

Thanks @amitchandak  for the solution.I have already subscribed your channel and like the way you are helping community.