Combining forecast of each month by Snapshot to Average of 3,12 month forecast?

[Anonymous]

Data consist of a forecast snapshot that has the future forecast for each month and year.

I want to get the average of 3 and 12 months of the forecast for each forecast snapshot into a column as below. How can I achieve this?

Date Snapshot 15 Jan 21
Average 3 Month Forecast (KG) = Average KG of (Feb to Apr 2021)

Average 12 Month Forecast (KG) = Average KG of (Feb 2021 to Jan 2022)

Date Snapshot 15 Feb 21
Average 3 Month Forecast (KG) = Average KG of (Mar to May 2021)

Average 12 Month Forecast (KG) = Average KG of (Mar 2021 to Feb 2022)

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

First you could add a column by dax.

Year-month = CONVERT( [Year]&"-"&[Month]&"-1",DATETIME)

Then create two measures.

average 3month = CALCULATE(AVERAGE([KG]),FILTER('Table',DATEDIFF([Date Snapshot],[Year-month],MONTH)<=3))

average 12month = CALCULATE(AVERAGE([KG]),FILTER('Table',DATEDIFF([Date Snapshot],[Year-month],MONTH)<=12))

The final show as follow:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Anonymous ,

this site explains almost everything in regards to date related calculations (including running totals or averages): Time patterns – DAX Patterns

Hopefully, this provides what you are looking.

Regards,

Tom

[Anonymous]

I read it, having a hard time understanding it. Is there a simplified version of what I am going to do?