Can I group only by months which are in sequence?

[Mpcz]

Hi All,

I'm quite new to the Power BI tool and I'm not sure how to perform the below grouping (if it's even possible).

I have a very simple dataset which looks like that:

I would like to aggregate the date by user and reduce the amount of entries. I tried to group by [User] with picking the MIN date from [Start Date] and MAX from [End Date] but the issues is that I would like to group only the months which are in sequence (are next to each other in calendar). Based on the above example I would like to achieve the below:

 I will really appreciate help to determine if thats even possible in Power Query Editor.

Thank you in advance! 

[tamerj1 · Accepted solution]

Hi @Mpcz 
Here is a sample file with the proposed solution https://we.tl/t-lKvepWSJSd

This includes creating two calculated columns 

Rank = 
VAR CurrentRant = 
    RANKX ( Data, Data[Start Date],, ASC, Dense )
VAR AllDates = 
    CALENDAR ( MIN ( Data[Start Date] ), MAX ( Data[End Date] ) )
VAR DatesWithRank = 
    ADDCOLUMNS ( 
        AllDates,
        "@Rank", RANKX ( AllDates, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),, ASC, Dense )
    )
VAR RealRank =
    MAXX ( FILTER ( DatesWithRank, [Date] = Data[Start Date] ), [@Rank] )
RETURN
    RealRank - CurrentRant

Index = RANKX ( Data, Data[Rank],, ASC, Dense )

Then the measures would simply be

StartDate = MIN ( Data[Start Date] )

EndDate = MAX ( Data[End Date] )

[Mpcz]

That's a big thank you! It's not the exact solution as I needed to do the transformation in power query but what I did, I replicated the steps from you measure in power query and it works like charm!

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Start Date], Data[End Date], User[User] ),
        "@sortordernumber",
            MAXX (
                FILTER ( 'Calendar', 'Calendar'[Month start CC] = Data[Start Date] ),
                'Calendar'[Month-Year sort order CC]
            ),
        "@previoussortordernumber",
            MAXX (
                FILTER (
                    'Calendar',
                    'Calendar'[Month start CC]
                        =
                        VAR _currentstartdate = Data[Start Date]
                        VAR _currentuser = User[User]
                        VAR _previousstartdate =
                            MAXX (
                                FILTER (
                                    Data,
                                    Data[Start Date] < _currentstartdate
                                        && Data[User] = _currentuser
                                ),
                                Data[Start Date]
                            )
                        RETURN
                            _previousstartdate
                ),
                'Calendar'[Month-Year sort order CC]
            )
    )
VAR _addconditioncolumn =
    ADDCOLUMNS (
        _newtable,
        "@condition",
            IF ( [@sortordernumber] - [@previoussortordernumber] = 1, 0, 1 )
    )
VAR _cumulatecondition =
    ADDCOLUMNS (
        _addconditioncolumn,
        "@cumulate",
            SUMX (
                FILTER (
                    _addconditioncolumn,
                    Data[Start Date] <= EARLIER ( Data[Start Date] )
                        && User[User] = EARLIER ( User[User] )
                ),
                [@condition]
            )
    )
RETURN
    SUMMARIZE (
        GROUPBY (
            _cumulatecondition,
            User[User],
            [@cumulate],
            "@minstartdate", MINX ( CURRENTGROUP (), Data[Start Date] ),
            "@maxenddate", MAXX ( CURRENTGROUP (), Data[End Date] )
        ),
        User[User],
        [@minstartdate],
        [@maxenddate]
    )

[amitchandak]

@Mpcz , refer if one of the 2 can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-More-Than-a-One-Day-...

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...