Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bryn987
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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))

View solution in original post

3 REPLIES 3
Villezekeviking
Helper II
Helper II

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
Helper II
Helper II

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:
 
michaelu1_0-1682530034493.png

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

amitchandak
Super User
Super User

@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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.