topic Re: DAX to compute the difference between date for specific items in DAX Commands and Tips

DAX to compute the difference between date for specific items

Ecan20 — Tue, 08 Jun 2021 12:59:09 GMT

Dear experts,
I have a table like the example below with product ID, StartDate, EndDate. I would like to calculate the difference between the last EndDate and the first StartDate per each iem ID, like shown in the DateDiff column. Is that possible with a measure? Appreciate if you can offer some help.

TIA!

ID	StartDate	EndDate	DateDiff
1	21-12-11 22:30	21-12-11 23:47	0.04167
1	21-12-11 23:47	21-12-11 23:30	0.04167
2	21-12-11 23:57	22-12-11 0:00	0.01250
2	22-12-11 0:00	22-12-11 0:14	0.01250
2	22-12-11 0:14	22-12-11 0:15	0.01250
3	22-12-11 0:27	22-12-11 0:40	0.02569
3	22-12-11 0:40	22-12-11 0:55	0.02569
3	22-12-11 0:55	22-12-11 1:04	0.02569
4	22-12-11 1:06	22-12-11 1:57	0.04514
4	22-12-11 1:57	22-12-11 2:03	0.04514
4	22-12-11 2:03	22-12-11 2:11	0.04514

Re: DAX to compute the difference between date for specific items

Anonymous — Tue, 08 Jun 2021 15:36:58 GMT

Ecan20

Are you sure you want a measure and not a calculated column? What should a measure return if there are many different ID's visible in the current context? Your picture suggests that you want a calculated column...

Re: DAX to compute the difference between date for specific items

Ecan20 — Tue, 08 Jun 2021 20:45:48 GMT

Anonymous you are right, a calculated column will work better for my case. I am not really familiar though, could you help me through the steps please? Thank you

Re: DAX to compute the difference between date for specific items

mahoneypat — Tue, 08 Jun 2021 21:57:31 GMT

Please try this column expression and convert the column to type decimal.

NewColumn =
VAR thisID = IDs[ID]
VAR minDT =
    MINX ( FILTER ( IDs, IDs[ID] = thisID ), IDs[StartDate] )
VAR maxDT =
    MAXX ( FILTER ( IDs, IDs[ID] = thisID ), IDs[EndDate] )
RETURN
    maxDT - minDT

Pat