Forum Discussion

Raj341's avatar
Raj341
Frequent Visitor
2 years ago

Last 30 days using dynamic date slicer

Hi All, I am attempting to achieve the following scenario: I have 3 columns: Date, Reasons, and Value. When I click on the date slicer in the matrix visual, the data should reflect the last 30 days...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Raj341 ,

For some unknown reason, I was unable to open the link you provided, so I created my own sample data for your reference, here it is:

Please note that it is better to add a table for creating the slicer without any relationship:

Then use this DAX to create the measure:

 

Measure = 
VAR _END = SELECTEDVALUE(Slicer[Date])
VAR _STRAT = _END - 30
RETURN
IF(
    ISFILTERED(Slicer[Date]),
    IF(
        MAX('Table'[Date]) <= _END && MAX('Table'[Date]) >= _STRAT,
        1,
        0
    ),
    1
)

 

Set up as shown in the figure below:

The final output is as below:


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Raj341's avatar
Raj341
Frequent Visitor
2 years ago

Hi v-junyant-msft
Thankyou for your response.
I am facing two challenges in your solution.
1. To utilize this, I must include the date column, otherwise, I will receive inaccurate results.
Please help me, we are very near to the solution.
2.When using the slicer table, I notice that all dates are displayed in the drop-down menu, rather than limiting it to the maximum date. This can be inconvenient for the client when trying to navigate to a specific date with available data
Note: Not able to use 

CALENDAR(MIN(Lines[Date]),MAX(Lines[Date])) for date calendar
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Raj341 ,

    I have changed my pbix.
    I add a measure like below (I set the start date 2023.12.16 as a fixed date):

    Measure_slicer = 
VAR _END = [StartDate] + 30
VAR _STRAT = [StartDate]
RETURN
IF(
    MAX('Slicer'[Date]) <= _END && MAX('Slicer'[Date]) >= _STRAT,
    1,
    0
)

    And put this measure into the slicer's filter:

    And now the slicer only has date from 2023.12.16 to 2024.1.15:


    And about this "To utilize this, I must include the date column, otherwise, I will receive inaccurate results." I don't really understand what the difficulty you are experiencing is, could you present your problem in a screenshot or some other clearer form?

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.