cancel
Showing results 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

Frequent Visitor

## Date Diff using date Filter

Hi community,
I want to create a measure that calculate the days between the oldest date of my table and a day selection based on date filter.

For insance, using the table below, the oldest date is "01/01/2022" and if I select in my date filter (which is from my same table) "01/09/2022" the result should be 8 days, or if I select in the filter "01/05/2022" the result would be 4.

Dates

Thanks for you help!

1 ACCEPTED SOLUTION
Resident Rockstar

What if no selection in the slicer? What do you want to display, the blank? Modify the table and column name accordingly

``````test =
VAR CurDate=SELECTEDVALUE(DateTable[Dates])
VAR MinDate = MINX(ALL(DateTable[Dates]),DateTable[Dates])
RETURN
DATEDIFF(MinDate,CurDate,DAY)``````

5 REPLIES 5
Community Support

Hi @JorgeMolano ,

Based on your description, try the following formula.

``````M_ =
VAR a =
CALCULATE ( MIN ( 'Table'[Date] ), ALL ( 'Table'[Date] ) )
VAR sel_b =
SELECTEDVALUE ( 'Table'[Date] )
RETURN
DATEDIFF ( a, sel_b, DAY )``````

If the problem is still not resolved, please point it out. Looking forward to your feedback.

Best Regards,
Henry

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

Frequent Visitor

Hi Vera,

Thanks for your support, I tried the measure you shared but only shows a consecutive number per each row, dont calculate the number of days between 2 different dates.

Best!

Super User

Hi,

The formula suggested by Vera should work for sure.  Make sure you write it as a measure (not a calculated column) and drag that measure to a card visual.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resident Rockstar

It worked here...maybe try ALL(DateTable) instead of the column? Or attach a sample file?

Resident Rockstar

What if no selection in the slicer? What do you want to display, the blank? Modify the table and column name accordingly

``````test =
VAR CurDate=SELECTEDVALUE(DateTable[Dates])
VAR MinDate = MINX(ALL(DateTable[Dates]),DateTable[Dates])
RETURN
DATEDIFF(MinDate,CurDate,DAY)``````

Announcements

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