Forum Discussion
Date filter - Relative Dates
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?
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))
3 Replies
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))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)
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.
