cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Wanted to know the maximum date from list of dates if there is gap based on Unique ID

Hi All,

I am new to Power bi and i wanted an help in getting the maximum date based on conditions. Below is my requirement.

I have list of unique ID's for which there are reported dates as well, however for few ID's there might me gap in the dates because they would have appeared today and later may be again in another date they would have appeared. So i wanted to know the Max date for unique id based on gap in date.

For example ABC has appeared on 11/06/2023 and 12/06/2023 and again on 20/06/2023 then my maximum date for ABC should show for 12/06/2023 and 20/06/2023

 Unique ID Reported Date Max Date ABC 11/06/2023 12/06/2023 ABC 12/06/2023 12/06/2023 ABC 20/06/2023 20/06/2023
1 ACCEPTED SOLUTION
Super User

@Gayathri1992
Yes, sure

``````Max Date =
VAR CurrentReportDate = 'Table'[Reported Date]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Unique ID] ) )
VAR TableOnAndAfter =
FILTER ( CurrentIDTable, 'Table'[Reported Date] >= CurrentReportDate )
VAR FilteredTable =
FILTER (
TableOnAndAfter,
VAR TableOnAndBefore =
FILTER (
TableOnAndAfter,
'Table'[Reported Date] <= EARLIER ( 'Table'[Reported Date] )
)
VAR DaysDiff =
COUNTROWS (
FILTER (
CALENDAR ( CurrentReportDate, 'Table'[Reported Date] ),
NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
)
)
RETURN
COUNTROWS ( TableOnAndBefore ) = COALESCE ( DaysDiff, 1 )
)
RETURN
MAXX ( FilteredTable, 'Table'[Reported Date] )``````

7 REPLIES 7
Regular Visitor

Depending on how large your dataset is, this could be a viable solution (create 2 columns + 1 measure).

is New Run of Dates =

``````// flags row as true when there's a run of new dates associated with it
VAR _currDt = 'Table'[Dates]
VAR _prevDate = MAXX( FILTER( 'Table', 'Table'[Dates] < _currDt ) , 'Table'[Dates])
VAR _logical = NOT _currDt = _prevDate + 1
RETURN _logical``````

Group ID =

``````// assigns an ID (1 , 2, .. X ) for each new group of consecutive dates
var _currDt = 'Table'[Dates]
var _iteratorTbl = FILTER( 'Table', 'Table'[Dates] <= _currDt )
var _uniqueDates = SUMMARIZE( _iteratorTbl, 'Table'[Dates], 'Table'[is New Run of Dates] )
RETURN
SUMX(
_uniqueDates,
IF( [is New Run of Dates], 1)
)``````

With the Group ID in place, you are able to identify groups of dates, and compute the max within the group...

Max of Run Group =

``````var _groups = VALUES( 'Table'[Group ID] )
var _tbl =
CALCULATETABLE(
VALUES( 'Table'[Dates] )
, ALL()
, 'Table'[Group ID] in _groups
)
return
MAXX( _tbl, 'Table'[Dates] )``````

Comments on the solution here:
- I'm not keen on adding calculated columns to large models so while this is functional I'd give consideration to how large your table is here. The low cardinality on the T/F column, and the fact that the ID column is a Int type it should be of minimal impact to most models
- Setting the group ID in the table greatly improves the calculation performance of the resultant measure, better to have your groups identified row-by-row before computing the MAX of the date

Super User

I agree with @Greg_Deckler  this is pretty complicated. I tried to produce a solution for this but not sure if it works 😅 I currently don't have access to my laptop to test it but I appreciate if you would be able to test from your end.

Calculated Column solution:

Max Date =
VAR CurrentReportDate = 'Table'[Report Date]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Unique ID] ) )
VAR TableOnAndAfter =
FILTER ( CurrentIDTable, 'Table'[Report Date] >= CurrentReportDate )
VAR FilteredTable =
FILTER (
TableOnAndAfter,
VAR TableOnAndBefore =
FILTER (
TableOnAndAfter,
'Table'[Report Date] <= EARLIER ( 'Table'[Report Date] )
)
VAR DaysDiff =
INT ( 'Table'[Report Date] - CurrentReportDate ) + 1
RETURN
COUNTROWS ( TableOnAndBefore ) = DaysDiff
)
RETURN
MAXX ( FilteredTable, 'Table'[Report Date] )

Super User

@Gayathri1992
I just tested it and seems to be working just fine. Of course the performance would be challenging. That you need to test with your full data.

``````Max Date =
VAR CurrentReportDate = 'Table'[Reported Date]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Unique ID] ) )
VAR TableOnAndAfter =
FILTER ( CurrentIDTable, 'Table'[Reported Date] >= CurrentReportDate )
VAR FilteredTable =
FILTER (
TableOnAndAfter,
VAR TableOnAndBefore =
FILTER (
TableOnAndAfter,
'Table'[Reported Date] <= EARLIER ( 'Table'[Reported Date] )
)
VAR DaysDiff =
INT ( 'Table'[Reported Date] - CurrentReportDate ) + 1
RETURN
COUNTROWS ( TableOnAndBefore ) = DaysDiff
)
RETURN
MAXX ( FilteredTable, 'Table'[Reported Date] )``````
Regular Visitor

Hi Tamerj,

This is working for me 🙂 and thank you for so much for the quick solution. However just another question on the same, how can we exclude weekends in between if there are weekends i dont want to consider those as gaps.

Super User

@Gayathri1992
Yes, sure

``````Max Date =
VAR CurrentReportDate = 'Table'[Reported Date]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Unique ID] ) )
VAR TableOnAndAfter =
FILTER ( CurrentIDTable, 'Table'[Reported Date] >= CurrentReportDate )
VAR FilteredTable =
FILTER (
TableOnAndAfter,
VAR TableOnAndBefore =
FILTER (
TableOnAndAfter,
'Table'[Reported Date] <= EARLIER ( 'Table'[Reported Date] )
)
VAR DaysDiff =
COUNTROWS (
FILTER (
CALENDAR ( CurrentReportDate, 'Table'[Reported Date] ),
NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
)
)
RETURN
COUNTROWS ( TableOnAndBefore ) = COALESCE ( DaysDiff, 1 )
)
RETURN
MAXX ( FilteredTable, 'Table'[Reported Date] )``````

Regular Visitor

Thank you so much 🙂 you are awesome.

It is working for me 🙂

Super User

@Gayathri1992 That's not a particularly easy problem to solve. You could potentially use something like Cthulhu to find the gaps and then proceed from there. Cthulhu - Microsoft Fabric Community

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors