Forum Discussion

JorgeMolano's avatar
JorgeMolano
Frequent Visitor
4 years ago
Solved

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi JorgeMolano 

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JorgeMolano 

     

    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)

     

  • JorgeMolano's avatar
    JorgeMolano
    Frequent Visitor
    4 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi JorgeMolano 

       

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

       

       

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      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.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.