Weekly Forecast

[Anonymous]

Hi all! I need some help here...

I created a post similar to this one, but I think the information was incomplete.

I have a table called "Sales", which is updated every day with sales per store and product. The last date filled with data is yesterday! Example:

Date	Store	Product	Qt	Total Sales
27/07/2020	Store 1	P2	1	150
27/07/2020	Store 1	P3	3	155
27/07/2020	Store 2	P3	2	110
28/07/2020	Store 1	P1	1	150
28/07/2020	Store 2	P1	2	160
28/07/2020	Store 2	P2	1	150
29/07/2020	Store 1	P3	3	155
29/07/2020	Store 2	P3	2	110
29/07/2020	Store 3	P1	1	99,9
30/07/2020	Store 2	P1	2	119,9
30/07/2020	Store 2	P2	1	109,9
30/07/2020	Store 3	P1	3	100

I need to create a chart with sales forecast per store, based on last days in this week / month / period of time. I would like to charte with stores in the rows, and date in the columns. For each date (if less than today), I must put the actual sales value. If the date is later, the field must be completed with the average of the last days in this chart (yellow marks in the table below). For security reasons, I can not put the true values...

How can I do this? There are some measure that I can create to do this?

Best regards,

Lucas

[Anonymous · Accepted solution]

Hi guys! I solve the problem doing this: - Create a table only with the stores values (Stores = VALUES(fVendas[store]) - Create a date table (d_Tempo = VAR DataMinima = MIN(fVendas[date]) VAR DataMaxima = MAX(fVendas[date])+365 RETURN CALENDAR(DataMinima,DataMaxima) - Create a new table summarizing daily sales per store; - Create a Forecast table joining store and d_tempo table(Forecast = CROSSJOIN(SELECTCOLUMNS(Store,"Loja",Store[Loja Correta]),SELECTCOLUMNS(d_Tempo,"Data",d_Tempo[Date])); - calculate the total sale in the actual week: Acumulado Semana TB1 = VAR SemanaAtual = 'Forecast'[Week Table 1] VAR LojaCorreta = 'Forecast'[Loja] VAR ActualYear = 'Forecast'[Ano] RETURN CALCULATE( SUM('Forecast'[Vendas do Dia]), FILTER( 'Forecast', 'Forecast'[Week Table 1] = SemanaAtual && 'Forecast'[Loja] = LojaCorreta && 'Forecast'[Ano] = ActualYear ) ) - And then, an if formula: Forecast Semanal = if( 'Forecast'[Data]

[v-yiruan-msft]

Hi @Anonymous ,

I just created a sample pbix file for you, please check if that is what you want.

1. Create a Date table

2. Create Stores table

Stores = VALUES('Sales'[Store]) 

3. Create relationships for these two tables with Sales table

4. Create a measure and create a line chart (Axis: date field from Date table   Legend: Store field from Stores table  Values: measure)

Measure = 
VAR _avesaleofOdays =
    CALCULATE (
        AVERAGE ( 'Sales'[Total Sales] ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Store] = MAX ( 'Stores'[Store] )
                && 'Sales'[Date] <= TODAY ()
        )
    )
RETURN
    IF (
        MAX ( 'Date'[Date] ) > TODAY (),
        _avesaleofOdays,
        CALCULATE ( SUM ( 'Sales'[Total Sales] ) )
    )

Best Regards

Rena

[dedelman_clng]

Can you expand on this?

---

@Anonymous wrote:

If the date is later, the field must be completed with the average of the last days in this chart (yellow marks in the table below). 

---

You need to be able to specify a formula for the forecast calculation. If you can, we should be able to make a measure that accounts for past and future.