Date Diff and group

[Anonymous]

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?

[mwegener]

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.

[Icey · Accepted solution]

Hi @Anonymous ,

I create an example.

ID	Open Date	Target Date
1	11/1/2019	11/19/2019
2	11/2/2019	11/21/2019
3	11/3/2019	11/18/2019
4	11/4/2019	11/20/2019
5	11/5/2019	11/22/2019
6	11/6/2019	11/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]

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]

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.