Moving average for dayli prodution forecast

[AbimaelC]

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: 

Date	Real Prodution	Fcst
01-nov	1000
02-nov	1500
03-nov	900
04-nov	1010
05-nov	950
06-nov	800	1072
07-nov	1500	1032
08-nov	1000	1032
09-nov	1063	1052
10-nov	1059	1063
11-nov	1055	1084
12-nov	1050	1135
13-nov	1046	1045
14-nov	1042	1055
15-nov	1038	1050
16-nov	1033	1046
17-nov	1029	1042
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!!

[AbimaelC]

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:

Date	Real Prodution
01-nov	1000
02-nov	1500
03-nov	900
04-nov	1010
05-nov	950
06-nov	800
07-nov	1500
08-nov	1000
09-nov	1063
10-nov	1059
11-nov	1055
12-nov	1050
13-nov	1046
14-nov	1042
15-nov	1038
16-nov	1033
17-nov	1029
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):

Date	Real Prodution	Fcst (estimated dayli production)
01-nov	1000
02-nov	1500
03-nov	900
04-nov	1010
05-nov	950
06-nov	800	1072
07-nov	1500	1032
08-nov	1000	1032
09-nov	1063	1052
10-nov	1059	1063
11-nov	1055	1084
12-nov	1050	1135
13-nov	1046	1045
14-nov	1042	1055
15-nov	1038	1050
16-nov	1033	1046
17-nov	1029	1042
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,

[MFelix]

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