Closing forecast (Real + Forecast) between dates with empty dates

[RRM_1977]

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

[amitchandak · Accepted 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.

[RRM_1977 · Accepted solution]

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!

[Ashish_Mathur]

Hi,

I'm not clear with your question.  What exact result are you expecting?

[amitchandak · Accepted 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.