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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
EmaVasileva
Helper V
Helper V

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.