DateDiff of the same Date column based in different condition

[I2SA]

Hi,

I try to make the difference between dates from the same column regarding statuts of an other column and for each Order_ID
I got an error message : "A table of multiple values was provided when a single value was expected."

Datediff2 =

var var_StartDate = calculate (lookupvalue(ORDER_STATUS[LOG_DATE],ORDER_STATUS[STATUS],"pending_reception"),ALLEXCEPT(ORDER_STATUS,ORDER_STATUS[ORDER_ID]))

var var_EndDate = calculate (lookupvalue(ORDER_STATUS[LOG_DATE],ORDER_STATUS[STATUS],"fulfilled"),ALLEXCEPT(ORDER_STATUS,ORDER_STATUS[ORDER_ID]))

RETURN

IF(

   NOT ISBLANK(var_EndDate),

   DATEDIFF(var_StartDate,var_EndDate,DAY)

)

Thanks for your help !

[v-tianyich-msft]

Hi @I2SA ,

Based on your description, I wonder that you want to calculate the duration of the log from preparation to completion, I created simple samples and you may check the results below:

Measure = var _StartDate = CALCULATE(MIN('Table'[LOG_DATE]),FILTER('Table','Table'[STATUS]="pending_reception"))
var _EndDate = CALCULATE(MAX('Table'[LOG_DATE]),FILTER('Table','Table'[STATUS]="fulfilled"))
return
IF(not ISBLANK(_EndDate),DATEDIFF(_StartDate,_EndDate,DAY),0)

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,

Community Support Team_ Scott Chang

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

[I2SA]

Thanks !
I think it's should work but I have an issue, for some cases I have several Fulfilled Dates for the same ID because I have log from different systems. I think I have to add an index in order to took the oldest one.
Do you know how to make this in Power Query ?

[v-tianyich-msft]

Hi @I2SA ,

You can try combining group by and index columns as needed.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[I2SA]

Almost, in my case, in your example Order ID is 1 for all your lignes and you have the same sample for other Order IDs

[v-tianyich-msft]

Hi @I2SA ,

Based on your description, I made a new attempt and you can check the following results:

Measure = 
VAR _AddColumn =
    SUMMARIZE(
        'Table',
        'Table'[ORDER_ID],
        "Mindate",
                MINX (
                    FILTER ( ALL('Table'), [ORDER_ID] = EARLIER ( [ORDER_ID] ) && [STATUS] = "pending_reception" ),
                    [LOG_DATE]
                ),
                 "Maxdate",
                MAXX (
                    FILTER (ALL('Table'), [ORDER_ID] = EARLIER ( [ORDER_ID]) && [STATUS] = "fulfilled" ),
                    [LOG_DATE]
                )
 
    )
VAR result =
    ADDCOLUMNS ( _AddColumn, "result", DATEDIFF ( [Mindate], [Maxdate], DAY ) )
return
SUMX(result,[result])

An attachment for your reference. Hope it helps!

Best regards,

Community Support Team_ Scott Chang

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

[lbendlin]

What made you use LOOKUPVALUE for this?

[I2SA]

I'am trying 🙂