Date functions in Power BI are not always easy. And sometimes they can be a little confusing… And sometimes, a little more than just a little..
For no other reason than pure serendipity, I have decided to record a quick video using a calculation I had to write to implement a four-month average logic that would exclude the current month.
So, if I am in Jan on my chart, I would find values for Feb, Mar, Apr and May, SUM them up and then divide the result by 4 to get the 4-month average.
Here is the link to the video
And here is the formula that is being used there:
4 Month Average =
var DatesForNext4Months = DATESINPERIOD('Date'[Date],
'Date'[Date] = SELECTEDVALUE('Date'[Date])
), 4, MONTH
var x = CALCULATE(DIVIDE([Secondary Sales Plan], 4), DatesForNext4Months)