Hello all, and thanks for the help you provide in a daily basis, it saved us a lot of times in moments of need.
I could not find any solution for this problem, and maybe someone here can hint me how to proceed. I have a date dimension linked to 2 fact tables:
1. Normal sales facts table linked by SQL query
2. Folder with daily excels with forecasts.
The issue is with this second one, I created a date column from the file name, and it contains a second date column with dispatch dates for the next n days. The idea is comparing the sales variation with the forecast in first day of the month, but I don't know how to prepare the model or which measure to use to achieve this.
Example.-
File 01/11 has forecast for 07/11 of 100k
Sales at 7/11 are 80k
Variation would be -20% from forecast.
I made it work for month aggregation (cumulative sales and last day forecast) using lastdate / Max (Date) measures, but I cannot make it work for daily basis, due to the dispatch date not being linked to the date table dimension. I tried creating a secondary relationship, and using USERELATIONSHIP measure, but I could not do it.
I hope the issue was understood, and someone can help me, since I cannot publish the file here :_(
Thank you very much, and have a very nice day.
Kind regards
