Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jdickson
New Member

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
1 ACCEPTED SOLUTION
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1669185371578.png

Pay attention to the order of the two dates

 

vjialluomsft_1-1669185442301.png

 

 

 

vjialluomsft_2-1669185478497.png

 

 

 

 

 

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.

View solution in original post

4 REPLIES 4
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1669185371578.png

Pay attention to the order of the two dates

 

vjialluomsft_1-1669185442301.png

 

 

 

vjialluomsft_2-1669185478497.png

 

 

 

 

 

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
Super User
Super User

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

 

BBF

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

@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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.