cancel
Showing results for
Did you mean:
Helper IV

## 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.

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
Super User
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())

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

2 REPLIES 2
Super User
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())

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helper IV

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors