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
    )

View solution in original post

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 218 members 2,228 guests
Please welcome our newest community members: