Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I am not able to get the result that I need in relation to a sales closing forecast for this year.
Let me explain:
1. On the one hand I have a measure with real sales ([CY Sales])
2. On the other hand I have a measure with the sales forecast ([Sales Forecast]), which is exactly the value of sales on the same day of the previous year to simplify (CALCULATE ([CY Sales]; SAMEPERIODLASTYEAR (Dim_Calendar [Date ]))
3. The model has a dimension of dates (Dim_Calendar)
4. There are days of the year 2019 that do not have sales (for example on weekends)
5. The problem arises when I want to join both measures, that is, I want to validate the date of the last sale (in the Fact_Table), and assume the values of current year [CY Sales] if it is less than or equal to that day, and the forecast values ([Sales Forecast]) if it is higher. As there are empty 2019 dates, I am not able to solve that problem.
6. In attached, the problem for a small interval. on days 16, 17 and 24 they should not appear in the last column (Real + Prevision)
Thanks in advance!!
Roberto
Solved! Go to Solution.
Use
CALCULATE ([CY Sales]; SAMEPERIODLASTYEAR (Fact[Date ]))
As, there no sales date, in fact, it will not give you data.
Check my example on last day
PriorDay Sales = CALCULATE(SUM(Sales[Sales_amount]),PREVIOUSDAY(Curr_Date[Sales_date])) PriorDay Sales when day = CALCULATE(SUM(Sales[Sales_amount]),PREVIOUSDAY(Sales[Sales_date]))
Second will have data only when the current day has sales. There some missing sales date in data.
Thank you so much for the answers.
I've worked on the problem and I solved the situation including a new column on the Dim_Calendar table (
So, the measure with the correct forecast is:
Thank you so much for the answers.
I've worked on the problem and I solved the situation including a new column on the Dim_Calendar table (
So, the measure with the correct forecast is:
Hi,
I'm not clear with your question. What exact result are you expecting?
Use
CALCULATE ([CY Sales]; SAMEPERIODLASTYEAR (Fact[Date ]))
As, there no sales date, in fact, it will not give you data.
Check my example on last day
PriorDay Sales = CALCULATE(SUM(Sales[Sales_amount]),PREVIOUSDAY(Curr_Date[Sales_date])) PriorDay Sales when day = CALCULATE(SUM(Sales[Sales_amount]),PREVIOUSDAY(Sales[Sales_date]))
Second will have data only when the current day has sales. There some missing sales date in data.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |