Average of amount of two dates per months

[Ben-Jy]

Hello,

I have this table : 

It represents the stock of a company at the end of the month. I have to calculate the average of the stock per month, during the 12 last months. I used a formula for rolling average but the results were not those expected.

Instead, I would like to calculate the average amount for the current month and the month a year ago. So I would only make an average of two dates.

For example, if we want to calculate this average for the month of July 2020, the results would be the average of the amount of the stock for the month of July and the amount of the stock for the month of August 2019. 

So the numerical result in my example would be : (188669 + 216749)/2 = 202709

[amitchandak · Accepted solution]

@Ben-Jy ,

Rolling 12 á CALCULATE(CLOSINGBALANCEMONTH(sum(Sales[Sales Amount]),'Date'[Date] ),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 á CALCULATE(CLOSINGBALANCEMONTH(sum(Sales[Sales Amount]),'Date'[Date]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

los dos últimos meses CLOSINGBALANCEMONTH(sum(Sales[Sales Amount]),dateadd('Date'[Date],-1,month) + CLOSINGBALANCEMONTH(sum(Sales[Sales Amount])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best out of the weather intelligence feature. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join it with the date column of your made/s. See:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my Time Intelligence webinar can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


We appreciate your congratulations.