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
moniqueeily
Regular Visitor

Relative Date Slicer With Overlapping Times

Hello everyone, 

 

I am attempting to create a slicer where someone could choose/single select Current Month, Prior Month, Current Week or Prior Week and it would update the whole dashboard for that specific time. We decided having buttons like this would make it easiest for the end users. 

moniqueeily_0-1679698384318.png

 

Here is my current codeing for the above slicer: 

IF('Date'[Relative Week Filter]=0,"Current Week",IF('Date'[Relative Week Filter]=-1,"Prior Week",IF(EOMONTH([Date],0)=EOMONTH(TODAY(),0),"Current Month",IF(EOMONTH([Date],0)=EOMONTH(TODAY(),-1),"Prior Month",""))))

 

My issue is when one selects Current Month it excludes the values associated with Prior Week or Current week which by the table below I can see the issue of why. 

moniqueeily_1-1679698384336.png

 

I can't seem to figure this out and I'm thinking there has to be a way to make this work. Any help is appreciated.

Thank you! 

1 REPLY 1
amitchandak
Super User
Super User

@moniqueeily , Yesterday I created this for a rolling solution but that should work for you, file is attached.

 

The second solution is in the blog.  You can use the min max calculation from the blog into the file to get what you want

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

 

MTD =
var _max = MAXX(allselected('Table'), 'Table'[Sales Date])
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 


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

 

 

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+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))

 

For the solution in file you need filter not date between

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.