Custom Forecast visual table with average Measure as estimate

[Anonymous]

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!

[AllisonKennedy]

Do 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.

[Anonymous]

Hi @AllisonKennedy 

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.