Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 DateCust No.Doc No.Prod A_VolB_Vol
9/25/2020 12:00:00 AMCHS156ML9589B 2690
9/26/2020 12:00:00 AMCHS156MM0667B 9821
9/27/2020 12:00:00 AMCHS156MM9946B 3464
9/28/2020 12:00:00 AMCHS156MM0876B 4657
9/29/2020 12:00:00 AMCHS156MM0987B 5198
9/30/2020 12:00:00 AMCHS156MM0456B 5566
9/25/2020 12:00:00 AMCHS156MM0316A2346 
9/26/2020 12:00:00 AMCHS156MM0320A5677 
9/27/2020 12:00:00 AMCHS156MM0367A5245 
9/28/2020 12:00:00 AMCHS156MM0456A3475 

 

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 DaysProdCust No.
9/29/2020 12:00:00 AMACHS156
9/30/2020 12:00:00 AMACHS156

 

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