Forum Discussion
Measure for optimistic/pessimistic scenario
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!
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.
4 Replies
- lbendlin
Super User
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.