Dynamic date difference

[Dodo96]

Hello,

I'm encountering some difficulties in calculating a dynamic date difference between a date in Table1 and the maximum date selected from a slicer. The slicer uses the Calendar table, which is directly connected to Table1.

What I'm trying to achieve is to categorize the data into three dynamic date ranges based on the selected max date:

Within 30 days from the selected max date

Between 31 and 60 days from the selected max date

Over 60 days from the selected max date

I would like the DAX expression to be fully dynamic, so that when a date is selected through the slicer, the calculation starts from that selected max date — not from today’s date. For example, if the selected max date is 31/12/2024, the calculation should be based on that date. If the selected date is 10/05/2025, then the calculation should adjust accordingly.

Thank you in advance 

[v-nmadadi-msft]

Hi @Dodo96 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks 

[v-nmadadi-msft]

Hi @Dodo96 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Dodo96 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

Hi @Dodo96  ,
Thanks for reaching out to the Microsoft fabric community forum.

I have tried out the DAX measure mentioned by @pankajnamekar25 and and it successfully produces the expected output.
Here is the output with my sample data

Please try it out and let us know if you have any issues.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[pankajnamekar25 · Accepted solution]

Hello @Dodo96 

DAX for calculated column

Date Range Category =

VAR SelectedMaxDate =

    CALCULATE (

        MAX ( 'Calendar'[Date] ),

        ALLSELECTED ( 'Calendar' )

    )

VAR DaysDiff =

    DATEDIFF ( Table1[Date], SelectedMaxDate, DAY )

RETURN

    SWITCH (

        TRUE(),

        DaysDiff <= 30, "Within 30 days",

        DaysDiff <= 60, "31 to 60 days",

        DaysDiff > 60, "Over 60 days",

        BLANK()

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.