# 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
``````