Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |