How to calculate using "latest 3 months data" even I add more data later

[paradika123]

Hi, everyone

I am still new at Power BI, maybe my english is not that good, I hope you understand my question..

So, I have this dataset (link below) where it have data for 3 months, I have to calculate AVERAGE on my data, I use this :

Average = CALCULATE(AVERAGE(StokSiloAll[Stok Keluar]);StokSiloAll[PLANT]=2404;StokSiloAll[TYPE]="PPC")

Then I realize, if use that code, then when I add some data later, ex : 1 month data later, it will calculate AVERAGE for 4 months instead of 3 months like i wanted. 

Please help me, what is the right code so it can calculate AVERAGE with latest 3 months data..

Thanks

Dataset link : https://goo.gl/qoZB29

[v-yuezhe-msft · Accepted solution]

@paradika123,

You can create the following measure. Then create rolling average measure based on the sum of stok measure following the guide in this blog.

 Sum of stok=CALCULATE(SUM(StokSiloAll[Stok Keluar]);StokSiloAll[PLANT]=2404;StokSiloAll[TYPE]="PPC")

Regards,
Lydia

[Greg_Deckler]

See my rolling months quick measure here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499

[iamprajot]

=
AVERAGEX (
DATESINPERIOD (
TableName[DateColumn],
LASTDATE ( TableName[DateColumn] ),
-3,
MONTH
),
SUM[AmountColumn])
)

[paradika123]

I'm interested with your solution but it still cant work properly in my case, can you help me by apply your code into mine (my code in my question above) ?
thanks 

[quentin_vigne]

Hi @paradika123

You need to use the relative date filtering so that you don't have to include your month in your formula anymore :

- Quentin