Filtering results on X Axis

[Anonymous]

Hi,

New to PowerBI so apologies if this should be obvious.

If I have the following data:

Fiscal YQ table

YQ	rownum
2020 Q 4	1
2020 Q 3	2
2020 Q 2	3
2020 Q 1	4
2019 Q 4	5
2019 Q 3	6
2019 Q 2	7
2019 Q 1	8

Then another table

YQ	Rep	Net Amount USD
2020 Q 4	John Smith	200
2020 Q 3	Jane Doe	500
2019 Q 4	John Smith	400

And I have a slicer on the YQ item, how would I ensure that:

A stacked bar chart only shows x numbers of items on the x axis.  Specifically, I wanted to show a max of last 4 quarters in the past if nothing is filtered (I have multiple years of data, the chart gets crowded, but I don't want to filter this information out as people may want to review items 3 years in the past).

I had created a measure:

QuarterSelectBounds = ((CALCULATE(MIN('AnalysisFiscalYearQuarterSort'[rownum]),ALLSELECTED(AnalysisFiscalYearQuarterSort[rownum]))+3))

and then another measure

Net Amount USD QuarterSelectBounded = IF(MIN('AnalysisFiscalYearQuarterSort'[rownum]) <= [QuarterSelectBounds], sum('Bookings'[Net Amount USD]), BLANK())

This works great, but only if I am using rownum on the x axis.  As soon as I swap it out to the YQ value, the min is applied on the line level.  I have done a lot of reading and feel like I might need to create some sort of disconnected table, but I'm a little lost at this point.  Any help?

Thanks

[amitchandak]

@Anonymous , Create a common qtr year table, join with both table . Create a Qtr year rank in that on year qtr

new column
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

measure
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 4 Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'), 'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-4 && 'Date'[Qtr Rank]<=max('Date'[Qtr Rank])))

[AllisonKennedy · Accepted solution]

If you want this to work for all YQ then you need to use YQ in the ALLSELECTED:

QuarterSelectBounds = ((CALCULATE(MIN('AnalysisFiscalYearQuarterSort'[rownum]),ALLSELECTED(AnalysisFiscalYearQuarterSort[YQ]))+3))