Measure for optimistic/pessimistic scenario

[Anonymous]

Hello,

In my data model I created a measure to sum the purchase for each week number for past years. I'm using a unrelated date table to acheive this because I have many other tables from many sources in my original model. I also created a measure to rank the purchase by week number.
Now for the future date I need to calculate the optimistic and pessimistic scenario from past years. I actually have about 3 years of data but I will have many more soon. For optmistic scenario I need to calculate the average of the best 2 years (rank 1 and 2). For pessimistic , it's the 2 worst years (of 5 last years).

https://www.dropbox.com/s/igaz4zqam5b7pbu/Test_purchase.pbix?dl=0 

With these data, the expected result for 2022, week #49 should be:
Optimistic = 26703 (average of rank 1 & 2)
Pessimistic = 19585 (average of rank 2 & 3)

How can I acheive this?

Thank you!

[lbendlin · Accepted solution]

Thank you for providing the pbix.  Unfortunately your sample data has too many holes to be useful.

Rank 3 is missing from most weeks, for example.

Anyway, here is a proposal based on your data  (note that I had to modify your data model.  I would also strongly recommend you should not use the built in date hierarchies)

Opt = 
var wt = year(today())*100+weeknum(TODAY(),1)
return  if (SELECTEDVALUE(DimDate[Year/week])<wt,BLANK(),
 var w= SELECTEDVALUE(DimDate[#Week])
 var a= SUMMARIZE(FILTER(ALL(DimDate),DimDate[#Week]=w),DimDate[Date].[Année],"Purch",[Purchase])
  var b = topn(2,a,[Purch],DESC)
 return AVERAGEX(b,[Purch]))

pbix attached.

[Anonymous]

Hello @lbendlin ,

Is it possible to sum de opt measure like this?

Thank you

[lbendlin]

yes, you can use a quick measure to add a cumulative sum.

[Anonymous]

Thanks a lot! It helps me to go forward!