cancel
Showing results for
Did you mean:
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.

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.

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!

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