Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering results on X Axis

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         ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago
    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))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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])))