Forum Discussion
RANKX for Rolling Years
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 ex...
Jihwan_Kim
Super User
Super UserHi,
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 ) )