Date filter - Relative Dates

[bryn987]

Is in the last Calendar Months

So, I like this filter as it shows me data from previous full months.  However, it does not show me the current months of data so you can't compare to see how your current month is doing to past months

Is in the last Months

This filter does show you the current month but it does not show you the full previous months.  So, I have to add an extra month to the filter and then just ignore the first month as it will be incomplete.

Any good way to have the best of both worlds, where I could have it show the last 3 complete months of data including today?  That way, I can get all of September, all of October, all of November and what I currently have in December?

[Villezekeviking]

I now this is already solved but I'm offering an alternative solution for those who might search and find this thread.

https://www.villezekeviking.com/dax-tables-calendar-and-time/

I have offset-columns from today that can be used to filter any report relative from today on days, weeks, months, quarters or years.

Unlike the relative date filtering, this can span any period, from historical data, over current periods and into the future.

[michaelu1]

I have a very simple solution for this.

I created a column on my date table called month offset.

Month Offset = DATEDIFF(TODAY(),'Date Table'[Date],MONTH)

This returns 0 for the current month, -1 for the month before, and so on.

Now I use this as filter on my visual like so:

Works like a charm!! (I have a simiar offset created for year as well)

[amitchandak · Accepted solution]

@bryn987 , refer if this approch can help

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

And few measures

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Rolling 2 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = Date(Year(_max), month(_max) -2, Day(_max))+1,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -3, Day(_max))+1
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-3)+1
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))