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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s