Thank you for posting your query onto the Forum.
Firstly as said mentioned about your calculations, yes they’re absolutely fine.
Now, as you mentioned about “I wonder why the distinct did not work as it supposes to”
The DISTINCT() function has worked correctly since it’s known for providing a unique list of values which we’ve already received in this case. Now, against the Product Name it’s showing you blank because against those Products there’s no Sales record for the Year = 2017 and now if we try to project/forecast against those we are by default gonna going to receive Blank values.
Distinct function removes the Blank values which are there within the column and not the adjacent to it.
So now when I compare my Year = 2017 records of Sales data with records of Year = 2018 we are getting the correct results in the visuals since there’s no data against the remaining products. I’m attaching the below screenshot for the reference.
Also to cross the numbers and records I added a Product Name as well as Year slicer in Sheet 1 of your analysis and when I selected any Product Name between the range from 15 to 44 and Year = 2017 it correctly showed the results but when I selected outside the range as mentioned above it showed the blank values. Below are the screenshots provided for the reference.
Also if you think that Distinct function hasn’t worked correctly in this case, then you can try to create another table with the formula given below and still you’ll receive the same results.
2018 Forecast 1 =
'Product'[Product Name] ) ,
"2018 Forecast" ,
CALCULATE( [Total Sales] , Dates[Year] = "2017" ) * 1.05 )
And lastly just for the reference I also added a calculated column of Sales Figures with the Year = 2017 and there were no records against it. Below is the screenshot provided for the reference.
As far as data is concerned, yes there is mismatch between the Excel and PBIX file provided but you can still proceed with the practice by presuming that the other products will be launched in the Year = 2018 and therefore against this products forecasting cannot be done since there are no past/performance records of it in order to do the Forecasting.
I’m also attaching the PBIX file for the reference.
Hoping you find this useful and helps you to go ahead with you practice.
Thanks & Warm Regards,
Scenario Analysis (1).pbix (547.1 KB)