Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a table
John | 20180412 |
Dave | 20180131 |
Emily | 20171226 |
John | 20180205 |
Emily | 20180314 |
Dave | 20180405 |
John | 20180218 |
John | 20180125 |
Dave | 20180401 |
Emily | 20180222 |
to
Name | Min Difference (days) |
John | 11 |
Dave | 4 |
Emily | 20 |
I am trying to use SUMX and SUMMARIZE but to not avail.
Is there any other way to get this? Using Calculated Column or Measure better?
@Greg_Deckler, @MattAllington @Phil_Seamark @quentin_vigne
Solved! Go to Solution.
This was a fun one. Took me a while, and I'll bet my solution isn't the prettiest or most efficient, but it works.
For each date, to get the nearest date:
Closest Date = VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY) VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) return SWITCH(TRUE(), ISBLANK(next), previous, ISBLANK(previous), next, daysFromNext > daysFromPrevious, previous, next)
Only a few swaps to get the days between that date and the nearest date:
Days From Closest Date = VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY) VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) return SWITCH(TRUE(), ISBLANK(next), daysFromPrevious, ISBLANK(previous), daysFromNext, daysFromNext > daysFromPrevious, daysFromPrevious, daysFromNext)
All that's left is a simple MIN()-based measure:
Minimum Difference = MIN(Data[Days From Closest Date])
Using variables really helps to break this problem down into its simplest components.
Hi @ngadiez,
The solution is achieved by "Summarize".
Measure 2 = MINX ( SUMMARIZE ( 'Table2', Table2[Name], Table2[Date], "dates", DATEDIFF ( [Date], CALCULATE ( MIN ( 'Table2'[Date] ), FILTER ( ALLEXCEPT ( 'Table2', Table2[Name] ), 'Table2'[Date] > MIN ( 'Table2'[Date] ) ) ), DAY ) ), [dates] )
Best Regards,
Dale
That's very good - we just need to compare each date with the next date!
You've inspired one last version from me (first time I've used NATURALINNERJOIN in a measure):
Min Difference (Days) v3 = VAR DateList = VALUES ( Data[Date] ) VAR DateListIndexed = ADDCOLUMNS ( DateList, "Index", RANKX ( DateList, Data[Date],, ASC ) ) VAR DateListIndexedPlusOne = SELECTCOLUMNS ( DateListIndexed, "Date2", Data[Date], "Index", [Index] + 1 ) VAR Joined = NATURALINNERJOIN ( DateListIndexed, DateListIndexedPlusOne ) RETURN MINX ( Joined, ABS ( [Date] - [Date2] ) )
Regards,
Owen
Hi @ngadiez,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @ngadiez,
The solution is achieved by "Summarize".
Measure 2 = MINX ( SUMMARIZE ( 'Table2', Table2[Name], Table2[Date], "dates", DATEDIFF ( [Date], CALCULATE ( MIN ( 'Table2'[Date] ), FILTER ( ALLEXCEPT ( 'Table2', Table2[Name] ), 'Table2'[Date] > MIN ( 'Table2'[Date] ) ) ), DAY ) ), [dates] )
Best Regards,
Dale
That's very good - we just need to compare each date with the next date!
You've inspired one last version from me (first time I've used NATURALINNERJOIN in a measure):
Min Difference (Days) v3 = VAR DateList = VALUES ( Data[Date] ) VAR DateListIndexed = ADDCOLUMNS ( DateList, "Index", RANKX ( DateList, Data[Date],, ASC ) ) VAR DateListIndexedPlusOne = SELECTCOLUMNS ( DateListIndexed, "Date2", Data[Date], "Index", [Index] + 1 ) VAR Joined = NATURALINNERJOIN ( DateListIndexed, DateListIndexedPlusOne ) RETURN MINX ( Joined, ABS ( [Date] - [Date2] ) )
Regards,
Owen
Hi @ngadiez
Here's another version, just a measure:
Min Difference (days) = VAR DateList = VALUES ( Data[Date] ) RETURN MINX ( GENERATE ( SELECTCOLUMNS ( DateList, "Date1", Data[Date] ), SELECTCOLUMNS ( EXCEPT ( DateList, { [Date1] } ), "Date2", Data[Date] ) ), ABS ( [Date1] - [Date2] ) )
EDIT: Possible improvement if we restrict Date2 > Date1:
Min Difference (days) v2 = VAR DateList = VALUES ( Data[Date] ) RETURN MINX ( GENERATE ( SELECTCOLUMNS ( DateList, "Date1", Data[Date] ), SELECTCOLUMNS ( FILTER ( DateList, Data[Date] > [Date1] ), "Date2", Data[Date] ) ), ABS ( [Date1] - [Date2] ) )
Note these assume there are no repeated dates, so the difference is the minimum difference between distinct dates.
Regards,
Owen
This was a fun one. Took me a while, and I'll bet my solution isn't the prettiest or most efficient, but it works.
For each date, to get the nearest date:
Closest Date = VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY) VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) return SWITCH(TRUE(), ISBLANK(next), previous, ISBLANK(previous), next, daysFromNext > daysFromPrevious, previous, next)
Only a few swaps to get the days between that date and the nearest date:
Days From Closest Date = VAR previous = CALCULATE(MAX(Data[Date]), FILTER(Data, [Date] < EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR next = CALCULATE(MIN(Data[Date]), FILTER(Data, [Date] > EARLIER(Data[Date]) && [Name] = EARLIER(Data[Name]))) VAR daysFromPrevious = DATEDIFF(previous, Data[Date], DAY) VAR daysFromNext = DATEDIFF(Data[Date], next, DAY) return SWITCH(TRUE(), ISBLANK(next), daysFromPrevious, ISBLANK(previous), daysFromNext, daysFromNext > daysFromPrevious, daysFromPrevious, daysFromNext)
All that's left is a simple MIN()-based measure:
Minimum Difference = MIN(Data[Days From Closest Date])
Using variables really helps to break this problem down into its simplest components.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |