Forum Discussion
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:
| 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!!
2 Replies
- MFelix
Super User
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 - AnonymousNot 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:
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,