Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 select...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago
    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())
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
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())
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.