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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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