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.

[ellichkas.]

I like the addition of video in the corner

[jazzista]

Great explanation of the days in period. You are right as people often times confuses this function with the datesbetween function .

[danny]

Thanks for the video. I am actually struggling however. I have followed your advice and created the table, and I am trying to collect the last 3 full months excluding the current month. The issue I am finding is that when I put -3 in from todays date it seems to still return dates in the current month. And really strangely It starts on the 2nd of the month! Not sure if you can help but any assistance would be great.

[Vaultdorr]

What if when you filter the dates, you only want to find dates of a specific category? Even if I filter and isolate the column with the date it doesn't accept that it is actually a date column:
Period], FILTER(Table1, Table1[Category]="Balls")), LASTDATE(Table1[Time Period])-1, -3, MONTH)