Forum Discussion
Weekly Forecast
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
- 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]
3 Replies
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
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.
- 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]
