cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Slicer Week/Month formula

Hi all,

Could you please help me with a dax formula, which I can use for Chliclet Slicer connected to a lince chart. The slicer has 4 options: 1 week, 2 weeks, 3 weeks, 1month. If 1 week is selected it should show the last 7 days including today, the 2 weeks option should show the last 14 days including today, 3 weeks -  the last 21 days incl.today, and 1 month- the last month incl.today. The dates should changing dynamically. 

5.png


 

 

 

 

I used this statement for 1 week: SWITCH (TRUE (),DATEDIFF ( 'date'[table], TODAY (), DAY ) <= 7, "1 Week",

DATEDIFF ( 'date'[table], TODAY (), DAY ) > 7 and it works, but for the 2 weeks,3w,1month- the logic isn't working.

Thank you.
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

I would have you measure only return values within the specified range, so

VAR __MinDate =
SWITCH([Slicer],
"1 Week",TODAY() - 7,
"2 Weeks",TODAY() - 14,
...
)
VAR __Date = MAX([Date])
RETURN
IF(__Date >= __MinDate,[measure calc],BLANK())

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Super User IV
Super User IV

I would have you measure only return values within the specified range, so

VAR __MinDate =
SWITCH([Slicer],
"1 Week",TODAY() - 7,
"2 Weeks",TODAY() - 14,
...
)
VAR __Date = MAX([Date])
RETURN
IF(__Date >= __MinDate,[measure calc],BLANK())

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @Greg_Deckler 


Thank you for the formula and apologies for the delayed response. I tried the formula and it works, but the result is not what I’m exactly looking for. Currently with the suggested formula if I selected the slicer option for 1 week I get the dates for the last seven days (26 of May – 3rd of June). If I choose “2 weeks” (14 days) it returns the week 14days ago (20-26 of May). I need when I select the slicer option for "2 weeks" to visualize the whole period starting 14 days ago until today (20 of May – 3rd of June).

Is there is a way to do that and to changing the dates dynamically. Or do you have an idea is it possible to have a slicer option which to combine the results for the 1 week and the second week values.

Thank you.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors