Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Gayathri1992
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 IDReported DateMax Date
ABC11/06/202312/06/2023
ABC12/06/202312/06/2023
ABC20/06/202320/06/2023
1 ACCEPTED SOLUTION

@Gayathri1992 
Yes, sure

1.png

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

 

View solution in original post

7 REPLIES 7
Matt_Hecht
Regular Visitor

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

Matt_Hecht_0-1686493391299.png

 

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...

 

Matt_Hecht_1-1686493591690.png

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
Super User
Super User

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

@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.

1.png

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

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.

@Gayathri1992 
Yes, sure

1.png

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

 

Thank you so much 🙂 you are awesome.

 

It is working for me 🙂

Greg_Deckler
Super User
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

 


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.