Forum Discussion
Calculate rolling average for future 7 days
Hi . I want to generate forecast which is 7 days rolling average of past days. This forecast should be generated for next 7 days based on latest date in [MV_START_DT_NEW] column . The average should be generated by grouping cluster , plant and product description. Below is the data sample and expected output columns highlighted in yellow
| Cluster_new | GSAP_PLANT_MAPPED | Product Description | MV_START_DT_NEW | Sum_Inv (BBL) | Forecasted value | Future forecast dates |
| NYHNE | Y826 | ETHANOL | 5/17/2024 | -119.3809524 | 10.33721088 | 5/24/2024 |
| NYHNE | Y826 | ETHANOL | 5/18/2024 | -133.5714286 | 10.33721088 | 5/25/2024 |
| NYHNE | Y826 | ETHANOL | 5/19/2024 | -134.8333333 | 10.33721088 | 5/26/2024 |
| NYHNE | Y826 | ETHANOL | 5/20/2024 | -137.7380952 | 10.33721088 | 5/27/2024 |
| NYHNE | Y826 | ETHANOL | 5/21/2024 | -195.4761905 | 10.33721088 | 5/28/2024 |
| NYHNE | Y826 | ETHANOL | 5/22/2024 | 920.6904762 | 10.33721088 | 5/29/2024 |
| NYHNE | Y826 | ETHANOL | 5/23/2024 | -127.33 | 10.33721088 | 5/30/2024 |
| RG3SW | Y001 | PUL | 5/17/2024 | 108.5952381 | 5.64 | 5/24/2024 |
| RG3SW | Y001 | PUL | 5/18/2024 | -39.04761905 | 5.64 | 5/25/2024 |
| RG3SW | Y001 | PUL | 5/19/2024 | -30.88095238 | 5.64 | 5/26/2024 |
| RG3SW | Y001 | PUL | 5/20/2024 | 26.4047619 | 5.64 | 5/27/2024 |
| RG3SW | Y001 | PUL | 5/21/2024 | -173.047619 | 5.64 | 5/28/2024 |
| RG3SW | Y001 | PUL | 5/22/2024 | 193.0238095 | 5.64 | 5/29/2024 |
| RG3SW | Y001 | PUL | 5/23/2024 | -124.547619 | 5.64 | 5/30/2024 |
| COLO | Y01Q | DIESEL | 5/17/2024 | -401.7857143 | 149 | 5/24/2024 |
| COLO | Y01Q | DIESEL | 5/18/2024 | -70.88095238 | 149 | 5/25/2024 |
| COLO | Y01Q | DIESEL | 5/19/2024 | -122.1904762 | 149 | 5/26/2024 |
| COLO | Y01Q | DIESEL | 5/20/2024 | -129.6428571 | 149 | 5/27/2024 |
| COLO | Y01Q | DIESEL | 5/21/2024 | -193.2619048 | 149 | 5/28/2024 |
| COLO | Y01Q | DIESEL | 5/22/2024 | -44.61904762 | 149 | 5/29/2024 |
| COLO | Y01Q | DIESEL | 5/23/2024 | -83.70952381 | 149 | 5/30/2024 |
Hi Anonymous,
You can try to use the following measure formulas if it sutiabel for your requirement:
Rolling AVG = VAR currDate = MAX ( Table1[MV_START_DT_NEW] ) RETURN AVERAGEX ( SUMMARIZE ( FILTER ( ALLSELECTED ( Table1 ), [MV_START_DT_NEW] <= currDate - 7 ), [Cluster_new], [GSAP_PLANT_MAPPED], [Product Description], "Total", SUM ( Table[Sum_Inv (BBL)] ) ), [Total] )Regards,
Xiaoxin Sheng
2 Replies
Hi Anonymous,
You can try to use the following measure formulas if it sutiabel for your requirement:
Rolling AVG = VAR currDate = MAX ( Table1[MV_START_DT_NEW] ) RETURN AVERAGEX ( SUMMARIZE ( FILTER ( ALLSELECTED ( Table1 ), [MV_START_DT_NEW] <= currDate - 7 ), [Cluster_new], [GSAP_PLANT_MAPPED], [Product Description], "Total", SUM ( Table[Sum_Inv (BBL)] ) ), [Total] )Regards,
Xiaoxin Sheng