topic Re: Count Rows based on a DATEDIFF Value greater than a specific amount in DAX Commands and Tips

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

jdickson — Thu, 10 Nov 2022 14:13:12 GMT

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

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

BeaBF — Thu, 10 Nov 2022 14:26:37 GMT

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

BBF

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

jdickson — Thu, 10 Nov 2022 14:38:01 GMT

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

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

BeaBF — Thu, 10 Nov 2022 14:44:04 GMT

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

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

v-jialluo-msft — Wed, 23 Nov 2022 06:40:49 GMT

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