Forum Discussion
Closing forecast (Real + Forecast) between dates with empty dates
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
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 (
Anterior a ultimo registro de venta = Dim_Calendario[Date] <= MAX( Fact_Facturacion[Fecha] ) which compares each Di_Calendar[Date] with the reference (dynamic) on the Fact table (where the last date with sales information is).So, the measure with the correct forecast is:
Ventas CY EST LE = SUMX( Dim_Calendario ; IF( ISBLANK( [Ventas CY] ) ; CALCULATE( [Ventas Prevision] ; Dim_Calendario[Anterior a ultimo registro de venta] = False ) ; BLANK() ))Then, one of the LE (LE01) is:I hope that can help another people with the same problem.Regards!
3 Replies
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 (
Anterior a ultimo registro de venta = Dim_Calendario[Date] <= MAX( Fact_Facturacion[Fecha] ) which compares each Di_Calendar[Date] with the reference (dynamic) on the Fact table (where the last date with sales information is).So, the measure with the correct forecast is:
Ventas CY EST LE = SUMX( Dim_Calendario ; IF( ISBLANK( [Ventas CY] ) ; CALCULATE( [Ventas Prevision] ; Dim_Calendario[Anterior a ultimo registro de venta] = False ) ; BLANK() ))Then, one of the LE (LE01) is:I hope that can help another people with the same problem.Regards!Hi,
I'm not clear with your question. What exact result are you expecting?
