Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Moving average for dayli prodution forecast

Hello,

 

We're working on a dashboard where we're trying to caculate the estimated dayli production for the next days based on the last 5 data of real production. However, when we don't have available data (future days) we want to avoid averaging zeros, meaning how can we switch the logic from average of the last 5 real data for the average of the last 5 forecast data for the day after today and so on.

 

Can you help us to formulate this logic?

 

Attached an example: 

DateReal Prodution Fcst
01-nov1000 
02-nov1500 
03-nov900 
04-nov1010 
05-nov950 
06-nov8001072
07-nov15001032
08-nov10001032
09-nov10631052
10-nov10591063
11-nov10551084
12-nov10501135
13-nov10461045
14-nov10421055
15-nov10381050
16-nov10331046
17-nov10291042
18-nov 1038
19-nov 1046
20-nov 1044
21-nov 1043
22-nov 1043
23-nov 1043
24-nov 1044
25-nov 1043
26-nov 1043
27-nov 1043
28-nov 1043
29-nov 1043
30-nov 1043

 

Thanks for your help!!

2 Replies

  • Hi

     

    Try the following code

     

    AVerage last 5 days =
    VAR temp_table =
        SUMMARIZE (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date]
                    >= MAX ( 'Table'[Date] ) - 5
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            ),
            'Table'[Date],
            "ActualValue",
                IF (
                    SUM ( 'Table'[Real Prodution ] ) = 0,
                    SUM ( 'Table'[Fcst] ),
                    SUM ( 'Table'[Real Prodution ] )
                )
        )
    VAR _result =
        AVERAGEX ( temp_table, [ActualValue] )
    RETURN
        _result

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

    Thanks so much for your help, but it didn't´work, i mean the column fcst is the calculated i would like to do, I have only the real production, the following table is the information I have:

    DateReal Prodution 
    01-nov1000
    02-nov1500
    03-nov900
    04-nov1010
    05-nov950
    06-nov800
    07-nov1500
    08-nov1000
    09-nov1063
    10-nov1059
    11-nov1055
    12-nov1050
    13-nov1046
    14-nov1042
    15-nov1038
    16-nov1033
    17-nov1029
    18-nov 
    19-nov 
    20-nov 
    21-nov 
    22-nov 
    23-nov 
    24-nov 
    25-nov 
    26-nov 
    27-nov 
    28-nov 
    29-nov 
    30-nov 

     

    And finally the column Fcst is the calculate (estimated dayli production for the next days based on the last 5 data of real production):

    DateReal Prodution Fcst (estimated dayli production)
    01-nov1000 
    02-nov1500 
    03-nov900 
    04-nov1010 
    05-nov950 
    06-nov8001072
    07-nov15001032
    08-nov10001032
    09-nov10631052
    10-nov10591063
    11-nov10551084
    12-nov10501135
    13-nov10461045
    14-nov10421055
    15-nov10381050
    16-nov10331046
    17-nov10291042
    18-nov 1038
    19-nov 1046
    20-nov 1044
    21-nov 1043
    22-nov 1043
    23-nov 1043
    24-nov 1044
    25-nov 1043
    26-nov 1043
    27-nov 1043
    28-nov 1043
    29-nov 1043
    30-nov 1043

     

    Thanks again,