Demystifying DATESINPERIOD (or How to Calculate N Months Average Excluding the Current Month)

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], 

                            FIRSTDATE(

                                NEXTMONTH(

                                    FILTER(ALL('Date'[Date]), 

                                        'Date'[Date] = SELECTEDVALUE('Date'[Date])

                                    )

                                )

                            ), 4, MONTH

                        )

var x = CALCULATE(DIVIDE([Secondary Sales Plan], 4), DatesForNext4Months)

return  x