Forum Discussion

jdickson's avatar
jdickson
New Member
3 years ago
Solved

Count Rows based on a DATEDIFF Value greater than a specific amount

I'm trying to create a measure that counts the rows of a table if the DATEDIFF between a date field in the table and today is greater than 11. The DAX query I have is below. (I'm trying to filter by a variable and that doesn't appear to be an option)

 

Scheduled Closing Trigger =
VAR d = FORMAT(TODAY(), "MM/DD/YYYY")
VAR bestKnown = CALCULATE(MAX('Loans'[Best Known Closing Date]))
VAR triggerValue = DATEDIFF(bestKnown,d,DAY)
VAR x = CALCULATE(
    COUNTROWS(Loans),
    FILTER(triggerValue > 11)
)

RETURN triggerValue
COUNTROWS
Filters
variables

4 Replies

    • jdickson's avatar
      jdickson
      New Member
      3 years ago

      Here is a sample table. It's just a list of dates. What I'm trying to do is count the number of rows in the table that have a Best Known Closing date greater than 11 days from today

       

      Best Known Cloisng Date

      12/01/2022

      12/10/2022
      01/15/2023
      11/15/2022
      11/30/2022
      • BeaBF's avatar
        BeaBF
        Super User
        3 years ago

        jdickson ok, try putting the Filter function inside, like this:

         

        Scheduled Closing Trigger =
        VAR d = FORMAT(TODAY(), "MM/DD/YYYY")
        VAR bestKnown = CALCULATE(MAX(Loans[Best Known Cloisng Date]))
        VAR triggerValue = DATEDIFF(bestKnown,d,DAY)
        VAR x = COUNTROWS(FILTER(Loans, triggerValue > 11))
         
        BBF
  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    Hi jdickson ,

     

    //DATEDIFF ( <Date1>, <Date2>, <Interval> )

    The result is positive if Date2 is larger than Date1.

    The result is negative if Date1 is larger than Date2.

    DATEDIFF – DAX Guide

    Pay attention to the order of the two dates

     

     

     

     

     

     

     

     

     

    Best Regards,

    Gallen Luo

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