# Consecutive Date grouping

• ### Question

• Consecutive Dates groups into one group:
If there is  day in between then we get a new group.

We need a DAX to generate the [Grouping] measure:
Afterwards I do a DistinctCount on the [Grouping] to get the Action "frequency" (using DAX)

Data source is:
User Date Action
A 1 1
A 2 1
A 3 0
A 4 1
A 5 1

B 1 1
B 2 0
B 3 0
B 4 1

And this is expected result from above :
User Grouping

A 2
B 2

many thanks

Tuesday, December 19, 2017 12:39 PM

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Group by contiguous dates.
http://www.mediafire.com/file/p04osbf82d7qg5q/12_19_17a.xlsx
http://www.mediafire.com/file/tlcg1l2odga2mn7/12_19_17a.pdf

Tuesday, December 19, 2017 4:24 PM
• Hi b_blase,

For user grouping, you can try below DAX formula:

```[Distinct Count for User Grouping] =
CALCULATE (
DISTINCTCOUNT ( 'YourTableName'[Action] ),
ALLEXCEPT ( 'YourTableName', 'YourTableName'[User] )
)```
Distinct Count for whole table:
```[Distinct Count For all] =
DISTINCTCOUNT ( 'YourTableName'[Action] )```
Distinct Count for user and date group:
```[Distinct Count for User and Date ] =
CALCULATE (
DISTINCTCOUNT ( 'YourTableName'[Action] ),
ALLEXCEPT ( 'YourTableName', 'YourTableName'[User] ,'YourTableName'[Date])
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, December 20, 2017 1:29 AM

### All replies

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Group by contiguous dates.
http://www.mediafire.com/file/p04osbf82d7qg5q/12_19_17a.xlsx
http://www.mediafire.com/file/tlcg1l2odga2mn7/12_19_17a.pdf

Tuesday, December 19, 2017 4:24 PM
• Hi b_blase,

For user grouping, you can try below DAX formula:

```[Distinct Count for User Grouping] =
CALCULATE (
DISTINCTCOUNT ( 'YourTableName'[Action] ),
ALLEXCEPT ( 'YourTableName', 'YourTableName'[User] )
)```
Distinct Count for whole table:
```[Distinct Count For all] =
DISTINCTCOUNT ( 'YourTableName'[Action] )```
Distinct Count for user and date group:
```[Distinct Count for User and Date ] =
CALCULATE (
DISTINCTCOUNT ( 'YourTableName'[Action] ),
ALLEXCEPT ( 'YourTableName', 'YourTableName'[User] ,'YourTableName'[Date])
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, December 20, 2017 1:29 AM
• Please kindly mark the correct solutions as answers, this could be beneficial to other community members who are facing similar issue. Your contribution is highly appreciated.

https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

Monday, February 26, 2018 12:20 PM
• Excel 2016 Pro Plus with PowerPivot. No Power Query used this time.
Grouped by consecutive dates.