dax grouping consecutive days

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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:

Add column:

Select your date column ->Add Column ->Date ->Subtract Days

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.

PBIX FILE https://www.dropbox.com/s/wsot9lfhq8sunlo/dax%20grouping%20consecutive%20days.pbix?dl=0

DATA FILE https://www.dropbox.com/s/gle4648a4c81kro/example.xlsx?dl=0

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

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:

Add column:

Select your date column ->Add Column ->Date ->Subtract Days

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.

PBIX FILE https://www.dropbox.com/s/wsot9lfhq8sunlo/dax%20grouping%20consecutive%20days.pbix?dl=0

DATA FILE https://www.dropbox.com/s/gle4648a4c81kro/example.xlsx?dl=0

Best Regards,

Lin

[Anonymous · Accepted solution]

Hello everyone,

tank you very much for your solution.

I want a solution only with Dax in Power pivot.

I had found a way.

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