Calculate DATEDIFF between earliest and latest date in same column by ID

[donaldo]

Hi all,

I've looked around and tried previously suggested solutions, but I don't seem to get the right results. I have the table below, with multiple duplicate IDs in the ImplementationLevel. They have a range of dates in the Month column.

I was hoping to calculate the earliest and latest date and then do a datediff to get the number of months from those two.

I've tried the DAX below:

R&D = 
VAR CurrentID = SavingsDistribution[Id]
VAR FirstM = CALCULATE(MAX(SavingsDistribution[Month]);FILTER(SavingsDistribution; SavingsDistribution[Id] = CurrentID))
VAR LastM = CALCULATE(MIN(SavingsDistribution[Month]);FILTER(SavingsDistribution; SavingsDistribution[Id] = CurrentID))
RETURN
DATEDIFF(FirstM;LastM;MONTH)

But the results are just 0 all the way down. 

Can anyone tell me whats wrong with my DAX or suggest another way to go about it?

[Anonymous]

Hi,

Hopefully I am not misunderstanding your question, but I think I have a solution for you. I have created a basic sample dataset that replicates yours:

I used three separate calculations to get at the Date Diff value. You could combine these into one if you wanted, but for the purposes of this post I think it's cleaner in three:

Min Date per Imp Level = MINX(FILTER(ALL(Sheet1),SELECTEDVALUE(Sheet1[Implementation Level]) = Sheet1[Implementation Level]),Sheet1[Date])

Max Date per Imp Level = MAXX(FILTER(ALL(Sheet1),SELECTEDVALUE(Sheet1[Implementation Level]) = Sheet1[Implementation Level]),Sheet1[Date])

Date Diff = DATEDIFF([Min Date per Imp Level],[Max Date per Imp Level],MONTH)

Here's the output:

Let me know if this works for you.

Thanks,

Ben

[donaldo]

Hi Ben,

Thanks so much for your suggestion.

The DAX you suggested is just returning blanks on my end. No errors.

My Month column is formatted as dates just showing month - year.

I'm not sure what to check or change.

Min Date per Imp Level = MINX(FILTER(ALL(SavingsDistribution);SELECTEDVALUE(SavingsDistribution[ImplementationLevel]) = SavingsDistribution[ImplementationLevel]);SavingsDistribution[Month])   


Max Date per Imp Level = MAXX(FILTER(ALL(SavingsDistribution);SELECTEDVALUE(SavingsDistribution[ImplementationLevel]) = SavingsDistribution[ImplementationLevel]);SavingsDistribution[Month])   

[PattemManohar]

@donaldo Could you please post your sample data in copiable format.

[donaldo]

Hi @PattemManohar

I've tried to provide a real sample here:

Id	ImplementationLevel	Month	Saving ('000)	Savings	R&D	Min Date per Imp Level	Max Date per Imp Level
7041AAFA-3BBE-4130-B7AB-C9997C3BD8A5	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	feb-15	4,038	4038	0		
95854737-12BE-4CCE-98D2-FB336980E2D8	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	jun-14	4,038	4038	0		
E70C3569-1BC8-4D76-BB3E-D2E17D1D45A7	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	feb-14	4,038	4038	0		
93E99F17-CA3D-4608-B3FE-DD308E3DE2C3	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	maj-14	4,038	4038	0		
80680AF3-EA4B-4908-8F84-D618DFA4E19E	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	aug-14	4,038	4038	0		
E9BC7A54-A7C3-4B70-8237-CDFF4B82BEB5	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	apr-14	4,038	4038	0		
7AB0937D-F0FA-4A4B-BF2D-902420E1B623	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	jul-14	4,038	4038	0		
D3B5F4E5-58CF-428A-9D6C-F3DC0F15BBDB	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	okt-14	4,038	4038	0		
C18BB873-8579-41B1-9B62-E6D37A9AA670	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	nov-14	4,038	4038	0		
0AEA2005-EABC-4C99-A1DC-B9C1BD7A5503	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	sep-14	4,038	4038	0		
2868DA7C-0D24-41C9-B3F0-2D9D6FBFBB17	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	dec-14	4,038	4038	0		
6CD28A90-28C0-4C67-9218-72BBC1569C9C	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	jan-15	4,038	4038	0		
F6C474DD-60AC-4037-931B-1E0AC4338F74	000013CC-D8DB-4C6F-B6E2-76DF0FF971CD	mar-14	4,038	4038	0		
8B113564-4C4A-464A-A81D-59542C9CFDAB	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	jun-19	4,667	4667	0		
AD744F08-AE65-4F48-BBF4-68A71863D53D	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	dec-19	4,667	4667	0		
A86DE390-1599-4EA9-90F4-2360ECFA67A2	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	okt-19	4,667	4667	0		
E3FECEDD-8325-48D2-893D-2AB6494A3E95	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	sep-19	4,667	4667	0		
0F217813-32B9-4274-8F19-63CF3534E061	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	aug-19	4,667	4667	0		
8E8303BB-5E47-4AF9-841A-FE668DD79DDF	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	jul-19	4,667	4667	0		
2B7F0BC6-CA66-4D81-9F8C-CB69ACDE274A	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	nov-19	4,667	4667	0		
C244B326-7B27-431C-BC7D-7B4C85650E4C	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	maj-19	4,667	4667	0		
7116ACAA-6651-4742-B83F-481B1FF6F890	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	apr-19	4,667	4667	0		
57A1DA5D-A97E-42CC-92BC-57F96406D262	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	feb-19	4,667	4667	0		
68ADD823-0172-419F-9C05-81841CA71DF9	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	mar-19	4,667	4667	0		
56B04CAA-E2C3-47AB-BEC2-8D96E0B5DD32	0000E5FE-39AA-4853-AD0D-ACEFC63CD43A	jan-19	4,667	4667	0		
32AB09FC-8793-4F43-B12B-675E5D44C762	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	jun-16	0,2	200	0		
39630BB2-9190-48D3-94E1-D38EFEE422C1	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	feb-16	0,2	200	0		
2CBBE8BC-C168-4200-A103-41782706385C	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	jan-16	0,2	200	0		
622ECD32-E3F9-4C6A-A92F-773C00665789	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	maj-16	0,2	200	0		
DC173577-F966-4171-BFFC-88243B6E2709	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	mar-16	0,2	200	0		
C10C29C5-422C-40AF-A160-0F4CE916AA8A	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	jul-16	0,2	200	0		
2423BE63-3F6E-46EC-A7CA-18A88CC4D976	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	aug-16	0,2	200	0		
6EBD235F-D978-4FEA-B795-97CDDD86A504	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	apr-16	0,2	200	0		
340BF3F8-C06B-4B8C-8F18-4F5BC2ACCF01	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	sep-16	0,2	200	0		
FBEA194F-DE6C-49E3-BA89-980DC0B376A2	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	okt-16	0,2	200	0		
D78EDB54-9202-4AC0-A69A-F5E326B96EEC	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	nov-16	0,2	200	0		
AD158654-99D4-4010-B0FA-5A75E0FD2196	000156BE-00E8-4D67-8B17-4DDD2F8C1F56	dec-16	0,2	200	0		
53796E45-A3FE-4393-9A28-F28D905E9755	0001A860-5DFE-492C-9764-F4B94B5E4BAD	dec-17	10	10000	0		
986BD1A8-AA9D-4DB9-AE9E-840E1EEB122F	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jan-17	10	10000	0		
516317FA-197B-48B1-A990-FD927EE73B21	0001A860-5DFE-492C-9764-F4B94B5E4BAD	feb-17	10	10000	0		
9CA01A42-ADEF-4B97-A6E4-B4496D3DDBD2	0001A860-5DFE-492C-9764-F4B94B5E4BAD	maj-17	10	10000	0		
720A2234-7B52-41C0-8F3A-84251EAC4A9D	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jul-17	10	10000	0		
6D9AEAFF-BA21-4234-BD9D-028B5B480941	0001A860-5DFE-492C-9764-F4B94B5E4BAD	sep-17	10	10000	0		
C16A52CA-0CF2-4667-8ABA-A831886946C1	0001A860-5DFE-492C-9764-F4B94B5E4BAD	apr-17	10	10000	0		
AA64B945-077B-4199-A4DC-F66F2C8D41F1	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jun-17	10	10000	0		
0EAD0286-D389-4BB7-9A19-4CBE2FCB5430	0001A860-5DFE-492C-9764-F4B94B5E4BAD	aug-17	10	10000	0		
917473B3-A281-41BB-AD33-A528CF703B36	0001A860-5DFE-492C-9764-F4B94B5E4BAD	okt-17	10	10000	0		
065D062A-1DF2-4CFB-980B-0225A321D643	0001A860-5DFE-492C-9764-F4B94B5E4BAD	apr-18	10	10000	0		
4EE88371-B2D5-4770-AC52-187671CBC9CF	0001A860-5DFE-492C-9764-F4B94B5E4BAD	feb-18	10	10000	0		
CD91AEBE-8322-42BF-8E1D-DF561339B8AA	0001A860-5DFE-492C-9764-F4B94B5E4BAD	mar-17	10	10000	0		
502927DD-4BBA-40E1-A320-F47F28B6E357	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jan-18	10	10000	0		
49F42281-D584-40E3-8F7B-0E0A9DECCA10	0001A860-5DFE-492C-9764-F4B94B5E4BAD	maj-18	10	10000	0		
D76CDDCB-1742-4015-8ADF-DBB734B70D27	0001A860-5DFE-492C-9764-F4B94B5E4BAD	dec-18	10	10000	0		
A08B32FA-FB47-4E23-BE7A-FD7DA0F72696	0001A860-5DFE-492C-9764-F4B94B5E4BAD	nov-17	10	10000	0		
A3A06711-5CBB-4475-89AF-6AC6F39EDCE3	0001A860-5DFE-492C-9764-F4B94B5E4BAD	aug-18	10	10000	0		
5142102E-4F1A-4FE5-8A5C-9E02656D800D	0001A860-5DFE-492C-9764-F4B94B5E4BAD	mar-18	10	10000	0		
18A606E0-D7F2-43C7-9F50-AF4E5899197C	0001A860-5DFE-492C-9764-F4B94B5E4BAD	nov-18	10	10000	0		
FEAE6E10-AB22-41E1-BB6C-5D025B0BD693	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jun-18	10	10000	0		
51B6E61A-F009-4012-9DA8-62F3CFA7D7CE	0001A860-5DFE-492C-9764-F4B94B5E4BAD	sep-18	10	10000	0		
9822ECAD-9889-4C93-9367-11164DC7F08E	0001A860-5DFE-492C-9764-F4B94B5E4BAD	jul-18	10	10000	0		
6DBAB3C2-200A-48D5-AE94-A7DC47651898	0001A860-5DFE-492C-9764-F4B94B5E4BAD	okt-18	10	10000	0		

[AlB · Accepted solution]

Hi @donaldo

I don't see any issues with your code. It's fine and it does work. I just tried it.

The sample data you've posted has only unique IDs, only one row per ID so obviously the difference between First and Last month is zero. That's correct. Maybe you have the same issue in your real data?

I added one additional row to your sample data with an additional date for one of the IDs and, magic, your code provides the correct answer.

The only minor thing is that you are using MIN for the Last month and MAX for the first month when I guess should be the other way around. But well, that's just nomenclature. 

[donaldo]

hi again @AlB

You put me on the right track! As my initial code was referencing the [Id] instead of [ImplementationLevel] column (which is the one with duplicates) it didn't work. I just had to replace the columns and it worked obviously, I just made an error.