cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## dax grouping consecutive days

Hello everyone,

the last days I tried to find a formula to group the consecutive days. I want to add a column with the values, which a shown on the right side in the picture. I tried several formulas with rankx, but I didn't find the right way.

I would be very glad, if you can help me with this problem.

Best regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: dax grouping consecutive days

Hi@ Friedrich

After my research ,You can try to do these follow my steps like below:

Step 1:

Sort Date ascending

And then Duplicate table

Step 2:

Add index column for two table

Basic table start from 1 and increment is  1

Duplicate table start from 0 and increment is  1

Step 3:

Merge two table

Step 4:

Expand the table

Step 5:

Step 6:

Duplicate the table in current state

Step 7:

Filter the table which values is ‘1’

Step 8:

Add index column start from 1 and increment is  1

Step 9:

Merge second duplicate with basic table

Step 10:

Expand the table

Step 11:

Select Table2(5).Index.1 column, Right click Fill->UP

Step 12:

Remove unnecessary column .

Result:

Here is demo , please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: dax grouping consecutive days

Hello everyone,

tank you very much for your solution.

I want a solution only with Dax in Power pivot.

The measures are:

Index := RANKX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]));'tab_Beispiel_gruppierung_Datum'[Datum];;ASC;Dense)

Date2 shift :=MINX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Datum]>EARLIER('tab_Beispiel_gruppierung_Datum'[Datum]));'tab_Beispiel_gruppierung_Datum'[Datum])

substraction Date := DATEDIFF('tab_Beispiel_gruppierung_Datum'[Datum];'tab_Beispiel_gruppierung_Datum'[Date2 shift];DAY)

Index2 :=IF([substraction Date]>1;1;0)

Index2 * Date :=[Index2]*[Datum]

Index3 :=RANKX('tab_Beispiel_gruppierung_Datum';[Index2 * Date];;ASC;Dense)

Index3 shift :=MAXX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Index]=EARLIER('tab_Beispiel_gruppierung_Datum'[Index])-1);'tab_Beispiel_gruppierung_Datum'[Index3])

Index4 :=IF([Index3 shift]=1;BLANK();[Index3 shift])

Index6 :=MAXX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Index]<=EARLIER([Index]));'tab_Beispiel_gruppierung_Datum'[Index4])

Index6 replace blank :=IF(ISBLANK([Index6]);1;[Index6])

If you find a better way with one or two Rankx formulas, letzt me know it.

Best regards

2 REPLIES 2
Community Support Team

## Re: dax grouping consecutive days

Hi@ Friedrich

After my research ,You can try to do these follow my steps like below:

Step 1:

Sort Date ascending

And then Duplicate table

Step 2:

Add index column for two table

Basic table start from 1 and increment is  1

Duplicate table start from 0 and increment is  1

Step 3:

Merge two table

Step 4:

Expand the table

Step 5:

Step 6:

Duplicate the table in current state

Step 7:

Filter the table which values is ‘1’

Step 8:

Add index column start from 1 and increment is  1

Step 9:

Merge second duplicate with basic table

Step 10:

Expand the table

Step 11:

Select Table2(5).Index.1 column, Right click Fill->UP

Step 12:

Remove unnecessary column .

Result:

Here is demo , please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: dax grouping consecutive days

Hello everyone,

tank you very much for your solution.

I want a solution only with Dax in Power pivot.

The measures are:

Index := RANKX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]));'tab_Beispiel_gruppierung_Datum'[Datum];;ASC;Dense)

Date2 shift :=MINX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Datum]>EARLIER('tab_Beispiel_gruppierung_Datum'[Datum]));'tab_Beispiel_gruppierung_Datum'[Datum])

substraction Date := DATEDIFF('tab_Beispiel_gruppierung_Datum'[Datum];'tab_Beispiel_gruppierung_Datum'[Date2 shift];DAY)

Index2 :=IF([substraction Date]>1;1;0)

Index2 * Date :=[Index2]*[Datum]

Index3 :=RANKX('tab_Beispiel_gruppierung_Datum';[Index2 * Date];;ASC;Dense)

Index3 shift :=MAXX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Index]=EARLIER('tab_Beispiel_gruppierung_Datum'[Index])-1);'tab_Beispiel_gruppierung_Datum'[Index3])

Index4 :=IF([Index3 shift]=1;BLANK();[Index3 shift])

Index6 :=MAXX(FILTER('tab_Beispiel_gruppierung_Datum';'tab_Beispiel_gruppierung_Datum'[FID]=EARLIER('tab_Beispiel_gruppierung_Datum'[FID]) && 'tab_Beispiel_gruppierung_Datum'[Index]<=EARLIER([Index]));'tab_Beispiel_gruppierung_Datum'[Index4])

Index6 replace blank :=IF(ISBLANK([Index6]);1;[Index6])

If you find a better way with one or two Rankx formulas, letzt me know it.

Best regards

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 25 members 1,005 guests
Recent signins: