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

[Gayathri1992]

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

[Matt_Hecht]

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

[tamerj1]

@Gayathri1992 

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] )

[tamerj1]

@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] )

[Gayathri1992]

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.

[tamerj1 · Accepted solution]

@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] )

[Gayathri1992]

Thank you so much 🙂 you are awesome.

It is working for me 🙂

[Greg_Deckler]

@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