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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Raj341
Frequent Visitor

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

Raj341_0-1711647645062.png
PBI Book

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

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1711677390565.png

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

vjunyantmsft_1-1711677440572.png

vjunyantmsft_2-1711677449146.png

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:

vjunyantmsft_3-1711677538050.png

The final output is as below:

vjunyantmsft_4-1711677560312.png

vjunyantmsft_5-1711677573370.png


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.

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

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:

vjunyantmsft_0-1711963089557.png

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

vjunyantmsft_1-1711963144956.png


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.

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.