Forum Discussion
Custom Forecast visual table with average Measure as estimate
Hi All
I am hoping to make use of daily average measure to forecast a figure for the remaining days of the month, I have managed to obtain a forecast table and a measure of daily average. But on certain condition it doesn't work so well.
I have a below report table with data that runs from 1st Sept till the latest data I have for each customer and product.
'Delivery' table:
| Delivered Date | Cust No. | Doc No. | Prod | A_Vol | B_Vol |
| 9/25/2020 12:00:00 AM | CHS156 | ML9589 | B | 2690 | |
| 9/26/2020 12:00:00 AM | CHS156 | MM0667 | B | 9821 | |
| 9/27/2020 12:00:00 AM | CHS156 | MM9946 | B | 3464 | |
| 9/28/2020 12:00:00 AM | CHS156 | MM0876 | B | 4657 | |
| 9/29/2020 12:00:00 AM | CHS156 | MM0987 | B | 5198 | |
| 9/30/2020 12:00:00 AM | CHS156 | MM0456 | B | 5566 | |
| 9/25/2020 12:00:00 AM | CHS156 | MM0316 | A | 2346 | |
| 9/26/2020 12:00:00 AM | CHS156 | MM0320 | A | 5677 | |
| 9/27/2020 12:00:00 AM | CHS156 | MM0367 | A | 5245 | |
| 9/28/2020 12:00:00 AM | CHS156 | MM0456 | A | 3475 |
From there I have created a forecast table that will populate the remaining days of the month for each [Cust No] and [Prod]. eg:
Only Prod A has data here and Prod B from above table already have data till end of the month.
'Forecasting' table:
| Remaining Days | Prod | Cust No. |
| 9/29/2020 12:00:00 AM | A | CHS156 |
| 9/30/2020 12:00:00 AM | A | CHS156 |
With this, I create a daily average measure for each product out from the first table, 'Delivery' table
Below an example for product A:
Daily Average = IF(
ISFILTERED('Forecasting'[Date]),
CALCULATE(SUM('Delivery'[A_Vol]))/CALCULATE(DISTINCTCOUNT('Delivery'[Date]), FILTER( 'Delivery', NOT(ISBLANK('Delivery'[A_Vol])))),
SUMX('Forecasting',
CALCULATE(SUM('Delivery'[A_Vol]))/CALCULATE(DISTINCTCOUNT('Delivery'[Date]), FILTER( 'Delivery', NOT(ISBLANK('Delivery'[A_Vol]))))
)
)
This is the portion where I have my problem, I want to visualize the forecast amount in table form, however I should see '0' for Product B as it has fulfilled till end of month but my table is still displaying the daily average for product B table, Prod B should be showing empty instead.
Also, is there anyway I can add this "Total" from the above forecast table to the actual total (fulfiled) from the Delivery table.
Thanks alot!
2 Replies
- AllisonKennedyCommunity ChampionDo you have a DimDate table? This could help make a few things easier: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Then, how have you created the 'Forecasting Table' ?
In order to be most helpful, we need to know what the raw, original data that you have is, and then what calculations you have done or want to do on top of that.- AnonymousNot applicable
Thank you for your response, attached is the pbix file i'm working on:
https://www.dropbox.com/s/rgvifehcvgaw1so/Report.pbix?dl=0
Raw Data:
https://www.dropbox.com/s/ynh89e9h5ijkbys/Delivery.csv?dl=0
For the forecast date table, I have actually use query to obtain the last date of each Prod and Customer. and using the last date to derive end of month of the last date. Subsequently the list of dates from the last date to the end of month date.