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 target date it is overdue. 

 

My question is how would i group those results after the date dif?

 

 

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.

4 Replies

  • 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.

  • Icey's avatar
    Icey
    Community 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.

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    did you solve your problem?


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

    Please give Kudos for support.