Last 30 days using dynamic date slicer

[Raj341]

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 of data, ending on January 15th, 2024.

The date range filter shows the correct results, and with the help of flags {0,1}, I am obtaining the expected outcomes. However, when I interact with the date slicer, the results are not as expected.

Note: The "SELECTEDVALUE" function is not working when I try to load the data from Excel. Therefore, I have resorted to using the MAX function.

"Instead of calculating specific measures for the last 30 days, need to utilize this DAX query as flags, which helps to reduce the number of calculations."

Last30days_flags =

VAR StartDate = MAX(Sheet1[Date])-30

VAR EndDate = MAX(Sheet1[Date])

RETURN

    IF(

       Sheet1[Date] <= EndDate && Sheet1[Date]>= StartDate,

        1,

        0

    )

When i dropped the calculation into filter, I am able to see 1 or 0 but not both
Please help me to achieve on this issue.
sharing workbook for reference

PBI Book

https://drive.google.com/file/d/1t9Cl3NXW1KDwUQw70ntiEIE0DORw-8Py/view?usp=sharing

[Ashish_Mathur]

Hi,

See if my solution, to a similar problem, in the attached file helps.

[Raj341]

Hi @Ashish_Mathur

Thank you for your response.

I replicated the same calculation.

Measure = CALCULATE([D], USERELATIONSHIP(Lines[Date], Calendar_dup[Date]), DATESBETWEEN(Calendar_dup[Date], MAX(Calendar_dup[Date]) - 6, MAX(Calendar_dup[Date])))

I followed the same process you did in data modeling, but I am encountering a different error:

Error: The USERELATIONSHIP function can only use the two column references participating in the relationship.

[Ashish_Mathur]

You are welcome.  Ensure that there is an inactive relationship between the Date column of the Lines table to the Date column of the Calendar_dup table.

[v-junyant-msft]

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]

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

[v-junyant-msft]

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.