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.

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

Hi Anonymous ,

 

create a calculated column (New column) with the overdue calculation.

Then you can treat the column like any other column.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

 

 

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

Hi Anonymous ,

 

i think you want something like this

 

DaysOverdue =
VAR Due =
    DATEDIFF ( 'Table'[Date]; TODAY (); DAY )
RETURN
    IF ( Due > 0; Due; BLANK () )

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.