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

[jdickson]

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

[v-jialluo-msft · Accepted solution]

^{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.}

[BeaBF]

@jdickson Hi! Can you paste some sample data or share the pbix?

BBF

[jdickson]

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]

@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