Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Diff and group

Hi,   I'm trying to work out how many records are out of target by using a datediff.   I have the target date and im going to use TODAY. The idea is that if Today's date is greater than the targe...
Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

    I create an example.

    IDOpen DateTarget Date
    111/1/201911/19/2019
    211/2/201911/21/2019
    311/3/201911/18/2019
    411/4/201911/20/2019
    511/5/201911/22/2019
    611/6/201911/30/2019

    Create measures like so:

     

    IsOverdue = IF ( TODAY () > MAX ( 'Table'[Target Date] ), 1, 0 )
    Count of IsOverdue = SUMX ( 'Table', [IsOverdue] )

     

    Or, you can create measures like so:

     

    DateDiff of Day = DATEDIFF ( MAX ( 'Table'[Target Date] ), TODAY (), DAY )
    IsOverdue 2 = IF ( [DateDiff of Day] > 0, 1, 0 )
    Count of IsOverdue 2 = SUMX ( 'Table', [IsOverdue 2] )

     

     

    Best Regards,

    Icey

     

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

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

I create an example.

IDOpen DateTarget Date
111/1/201911/19/2019
211/2/201911/21/2019
311/3/201911/18/2019
411/4/201911/20/2019
511/5/201911/22/2019
611/6/201911/30/2019

Create measures like so:

 

IsOverdue = IF ( TODAY () > MAX ( 'Table'[Target Date] ), 1, 0 )
Count of IsOverdue = SUMX ( 'Table', [IsOverdue] )

 

Or, you can create measures like so:

 

DateDiff of Day = DATEDIFF ( MAX ( 'Table'[Target Date] ), TODAY (), DAY )
IsOverdue 2 = IF ( [DateDiff of Day] > 0, 1, 0 )
Count of IsOverdue 2 = SUMX ( 'Table', [IsOverdue 2] )

 

 

Best Regards,

Icey

 

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