topic Re: DateDiff of the same Date column based in different condition in DAX Commands and Tips

DateDiff of the same Date column based in different condition

I2SA — Fri, 20 Oct 2023 12:35:14 GMT

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 !

Re: DateDiff of the same Date column based in different condition

lbendlin — Sun, 22 Oct 2023 17:32:09 GMT

What made you use LOOKUPVALUE for this?

Re: DateDiff of the same Date column based in different condition

Anonymous — Mon, 23 Oct 2023 07:12:28 GMT

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.

Re: DateDiff of the same Date column based in different condition

I2SA — Tue, 24 Oct 2023 08:19:38 GMT

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

Re: DateDiff of the same Date column based in different condition

I2SA — Tue, 24 Oct 2023 12:30:36 GMT

I'am trying 🙂

Re: DateDiff of the same Date column based in different condition

Anonymous — Wed, 25 Oct 2023 03:05:03 GMT

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.

Re: DateDiff of the same Date column based in different condition

I2SA — Thu, 26 Oct 2023 08:14:42 GMT

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 ?

Re: DateDiff of the same Date column based in different condition

Anonymous — Thu, 26 Oct 2023 08:30:19 GMT

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.