cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Grouping by consecutive dates into date ranges

HI @lkarolak

 

Please change the formua of Series Start as follows

 

SeriesStart =
VAR PreviousDate =
    CALCULATE (
        MAX ( TableName[Date ] ),
        FILTER ( TableName, TableName[Date ] < EARLIER ( TableName[Date ] ) )
    )
VAR PreviousName =
    CALCULATE (
        FIRSTNONBLANK ( TableName[Name], 1 ),
        FILTER ( TableName, TableName[Date ] = PreviousDate )
    )
VAR myrank =
    RANKX ( TableName, TableName[Date ],, ASC, DENSE )
RETURN
    IF (
        PreviousDate
            <> TableName[Date ] - 1
            && TableName[Name] = PreviousName,
        myrank + 1,
        myrank
    )
Super User
Super User

Re: Grouping by consecutive dates into date ranges

lkarolak Frequent Visitor
Frequent Visitor

Re: Grouping by consecutive dates into date ranges

Thank you @Zubair_Muhammad, that was it!!

Highlighted
Paul_PowerBI Regular Visitor
Regular Visitor

Re: Grouping by consecutive dates into date ranges

Hi @Zubair_Muhammad,

 

I have a very similar problem with one critical difference, my source data can have multiple transaction on the same date. See below:

 

Grouping by Consecutive Dates.PNGOriginal RequirementGrouping by Consecutive Dates 2.PNGMy Requirement

In my requirement, I want to ignore the second transaction for Mark on the 1st March 2018. This is the result I need:

 

DateNameSeriesStartSeriesBoundariesLast Date
Thursday, 01 March 2018Mark1Series StartThursday, 01 March 2018
Thursday, 01 March 2018Mark1  
Saturday, 03 March 2018Mark3Series StartSaturday, 03 March 2018
Wednesday, 07 March 2018John3Series StartThursday, 08 March 2018
Thursday, 08 March 2018John4Series End 
Thursday, 15 March 2018Steve5Series StartThursday, 15 March 2018
Tuesday, 20 March 2018Mark6Series StartThursday, 22 March 2018
Wednesday, 21 March 2018Mark7  
Thursday, 22 March 2018Mark8Series End 
Sunday, 01 April 2018Claudia9Series StartSunday, 01 April 2018
Tuesday, 03 April 2018Claudia11Series StartTuesday, 03 April 2018

 

How would you change your code to handle this situation? I have re-uploaded your solution with my requirement here:

 

My Requirement

 

Thanks