RANKX for Rolling Years

[Andrea_Jess]

Hi All,

I have a disconnected date slicer that i'm using on a products graph. The graph is a line chart that returns 3 years of data based on slicer selected and it shows it by product.

For example, if i select FY21, the line graph shows FY19, FY20, and FY21 for each product. At the moment, i can see all product but i want to only show top 5 products based on the aggregated sum of Amount across the three years. When i use TOPN, i'm not getting correct products. 

I then thought to use RANKX but because i have a disconnected slicer, i'm not sure how to invoke it so that it ranks based on aggregated sum of past 3 years. 

The measure i'm using to filter the graph to last 3 years is: 

VAR Selected = SELECTEDVALUE(Disconnected[FiscalYear])

RETURN
IF(AND(MAX(Data[Fiscal year]) <= Selected, MAX (Data[Fiscal year] ) >- Selected-2),1,0)

I have applied this has a visual filter on the graph. 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I have created a sample pbix file based on your explanation. Instead of recent three years, I tried to created "recent three months". But the concept is the same, and I hope you can apply to your data model. Or, please share your sample pbix file, and then more accurate solution can be created.

Qty total last three months: =

VAR selectedlastdate =

LASTDATE ( 'Dates Slicer'[Date] )

VAR lastthreemonth =

DATESINPERIOD ( Dates[Date], selectedlastdate, -3, MONTH )

RETURN

CALCULATE ( SUM ( Data[Qty] ), KEEPFILTERS ( lastthreemonth ) )

Top five products Qty total last three months: =

VAR newtable =

FILTER (

ADDCOLUMNS (

SUMMARIZE ( ALL ( Data ), Dates[Month & Year], 'Product'[Product] ),

"@lastthreemonths", [Qty total last three months:]

),

Dates[Month & Year] = VAR currentmonth = Dates[Month & Year] RETURN currentmonth

)

VAR topfiveproducts =

TOPN ( 5, newtable, [@lastthreemonths], DESC )

RETURN

CALCULATE ( [Qty total last three months:], KEEPFILTERS ( topfiveproducts ) )