Calc date difference between rows by unique ID

[gaspar13]

Hello,

I am trying to calculate the difference between the max and min dates (in the [As of Date] field) across multiple rows. The issue is that I need the difference to be calculated for each unique [Internal ID] (another field) and the difference should be between the minimum of Status "Unqualified" and the maximum of Status "Closed - Won", "Closed - Pending" and "Closed - Confirmed".

The table is below and here is the code I have so far (with the help of this forum). But the measure returns the same number for all records.

Not sure if I should break this up into tables or can this be done in measure? From the data in this table, I would expect this result:

Internal ID	DateDiff	Calc
12345	13	max of 1/12/22, 1/13/22, 1/14/22 less min of 1/1/22, 1/2/22, 1/3/22

Internal ID	Status	As of Date (M/D/YY)
12345	Unqualified	1/1/22
12345	Unqualified	1/2/22
12345	Unqualified	1/3/22
12345	Closed - Won	1/13/22
12345	Closed - Confirmed	1/12/22
12345	Closed - Pending	1/12/22
12345	Closed - Pending	1/14/22

My measure so far...

DateDiff =
VAR Type1 =
    CALCULATE (
        MIN ( 'AsOfGrossSalesRecordResults667'[As Of Date].[Date] ),
        'AsOfGrossSalesRecordResults667'[Status] = "Unqualifed"
    )
VAR Type2 =
    CALCULATE (
        MAX ( 'AsOfGrossSalesRecordResults667'[As Of Date].[Date] ),
        'AsOfGrossSalesRecordResults667'[Status] = "Closed - Lost",
        'AsOfGrossSalesRecordResults667'[Status] = "Closed - Pending",
        'AsOfGrossSalesRecordResults667'[Status] = "Closed - Won"
    )
RETURN
    FORMAT ( ( Type2 - Type1 ), "Standard" )

Thanks in advance!

[amitchandak]

@gaspar13 , based on what I got, Try like

DateDiff =
VAR Type1 =
CALCULATE (
MIN ( 'AsOfGrossSalesRecordResults667'[As Of Date].[Date] ),
Filter('AsOfGrossSalesRecordResults667', 'AsOfGrossSalesRecordResults667'[Status] = "Unqualifed"), allexcept('AsOfGrossSalesRecordResults667','AsOfGrossSalesRecordResults667'[Internal ID])
)
VAR Type2 =
CALCULATE (
MAX ( 'AsOfGrossSalesRecordResults667'[As Of Date].[Date] ),
Filter('AsOfGrossSalesRecordResults667', 'AsOfGrossSalesRecordResults667'[Status] = "Closed - Lost" ||
'AsOfGrossSalesRecordResults667'[Status] = "Closed - Pending" ||
'AsOfGrossSalesRecordResults667'[Status] = "Closed - Won" ) , allexcept('AsOfGrossSalesRecordResults667','AsOfGrossSalesRecordResults667'[Internal ID])
)
RETURN
FORMAT ( ( Type2 - Type1 ), "Standard" )

[gaspar13]

Hi Amit,

It's close, but I'm getting results like 44,680 for a lot of records with different Internal IDs (see below).

I'm putting the DateDiff measure plus the Internal ID into a table viz.