Forum Discussion
To create a Date Slicer
I wanted to create a date slicer of Today, last 2 days ,this week and this month
can anybody pls explain
And the slicer order should be in
Today
Last 2 days
this week and
this month
PowerBI powerbi3 BI Powerbidesign Anonymous
Hi Anonymous ,
1. first use enter data to create a new table like:2. then please new a measure like:
Measure = VAR _SLICER = MAX('Table'[Slicer]) VAR _TODAY = TODAY() VAR _DATE = MAX('Calendar'[Date]) VAR _LAST_2_DAYS = TODAY() - 2 VAR _FIRSTDATEOFWEEK = TODAY() - WEEKDAY(TODAY(),2) VAR _MONTH = MONTH(_TODAY) VAR _YEAR = YEAR(_TODAY) VAR _FILTER = SWITCH( TRUE(), _SLICER = "Today"&&_DATE=_TODAY,1, _SLICER = "Last 2 days"&&_DATE>=_LAST_2_DAYS&&_DATE<_TODAY,1, _SLICER = "This week"&&_DATE>=_FIRSTDATEOFWEEK&&_DATE<_FIRSTDATEOFWEEK+7,1, _SLICER = "This month"&&_YEAR=YEAR(_DATE)&&_MONTH=MONTH(_DATE),1 ) RETURN _FILTER3. apply it to the filter:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
8 Replies
Anonymous , You need a date table and one table with all these values
measure
Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType])
"today",today()
"Last 2 days",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"this week",_max -1*weekday(_max,-1)+1 ,
"this month",eomonth(_max,-1)+1 ,
BLANK())
return
CALCULATE(Sum(Sales[Sales[Amount]),DATESBETWEEN('Date'[Date],_min,_max))refer for the period type
https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79
amitchandak can you pls explain
and i even want to add custom date picker also
like and it should be in this order:
Today
Last 2 days
this week
this month and
custom(date picker)
when i clicked custom (date picker) it should display date picker
any idea?
and i even created a date picker but when i click year it doesn't show all the years@WarrenBelz @iAm_ManCat Anonymous BI andres777
Hi Anonymous ,
1. first use enter data to create a new table like:2. then please new a measure like:
Measure = VAR _SLICER = MAX('Table'[Slicer]) VAR _TODAY = TODAY() VAR _DATE = MAX('Calendar'[Date]) VAR _LAST_2_DAYS = TODAY() - 2 VAR _FIRSTDATEOFWEEK = TODAY() - WEEKDAY(TODAY(),2) VAR _MONTH = MONTH(_TODAY) VAR _YEAR = YEAR(_TODAY) VAR _FILTER = SWITCH( TRUE(), _SLICER = "Today"&&_DATE=_TODAY,1, _SLICER = "Last 2 days"&&_DATE>=_LAST_2_DAYS&&_DATE<_TODAY,1, _SLICER = "This week"&&_DATE>=_FIRSTDATEOFWEEK&&_DATE<_FIRSTDATEOFWEEK+7,1, _SLICER = "This month"&&_YEAR=YEAR(_DATE)&&_MONTH=MONTH(_DATE),1 ) RETURN _FILTER3. apply it to the filter:
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
