Power BI Desktop groups consecutive dates

Scenario:

It is always necessary to group consecutive dates before proceeding to the next step of calculation when we are at work, which can be resolved by using the DAX function or Power Query in Power BI Desktop.

 

The specific operations are:

1. Use the DAX function combination.
2. Open Power Query, add Index to the table, subtract date and combine multiple steps of the table and the table to complete.

 

Sample data:

Table Date1：

 

 

Expected Results:

 

 

Method 1：Use the DAX function to group

1. Use RANKX function to create a calculated column to sort the dates

 

 

rank = RANKX(ALL(Table1),'Table1'[date],,ASC)

 

 

        

 

2. Create the following measures to get the groups based on consecutive dates range. 

Measure [if]

By subtracting the current date from the next date, it is a continuous date if it is equal to 1, and it is not a continuous date if it is not equal to it, and it is judged by the IF() function.

 

 

 

 if =

var _current = MAX(Table1[date])

var _next = CALCULATE (MAX ('Table1'[date]),

FILTER (ALL(Table1), 'Table1'[rank] = MAX ('Table1'[rank]) + 1)

)

var _datediff =

DATEDIFF (_current, _next, DAY )

RETURN

IF ( _datediff = 1, 0,1 )

 

 

Measure [accumulative]

 Accumulate the value of [IF] by date.

 

 

 accumulative =
 SUMX (
     FILTER ( ALL ( Table1 ), 'Table1'[date] <= MAX ( 'Table1'[date] ) ),
     [if]
 )

 

 

Measure [Group_consecutive days]

      Compare the current [accumulative] with the previous [accumulative], If not equal, it is the current value, if not, it is the current value + 1.

 

 

Group_consecutive days =
 VAR _current =
     MAXX (
         FILTER ( ALL ( Table1 ), 'Table1'[rank] = MAX ( 'Table1'[rank] ) ),
         [accumulative]
     )
 VAR _previous =
     MAXX (
         FILTER ( ALL ( Table1 ), 'Table1'[rank] = MAX ( 'Table1'[rank] ) - 1 ),
         [accumulative]
     )
 RETURN
     IF ( _current <> _previous, _current, _current + 1 )

 

 

Result：

 

 

Method 2: Use Power Query to group consecutive dates.

1. Add Index for Date1: Add Column –> Index Column

Table Date1 — Index Column — From 0.

 

2. Click on Add Column — Custom Column.

Enter the following code:

#”Added Index” [date] {[Index]+1}

 

3. Select the newly formed column — right click — Replace Errors.

 

Replace it with null.

 

4. First select [date2], then select [date], then click Add Column –> Date –> Subtract Days.

 

5. Click on the table Date1 — Copy — to form a new table Date2.

 

6. Click the column [Subtraction] of the new table Date2 and filter it out to 1.

 

 

7. For table Date2 — Add Column –> Index Column –> From 1

 

8. Merge table Date1 and Date2 to form Merge1, Home –> Merge Queries –> Merge Queries as New

 

9. In the new table Merge1, click the extension icon of [Date2.1] in the red box — only select [Index.1].

 

10. In the table Merge1, click on the column [date] — Sort Ascending.

 

        

 

11. Right click on the column [Date2.1.Index.1] of table Merge1 — Fill — Up.

 

 

12. Delete redundant columns.

 

 

Result：

 

 

Summarize:

Above we have realized the grouping of different consecutive date periods by means of DAX and Power Query respectively, hope this article can help you to solve a similar problem.

 

 

Author: Liu Yang

Reviewer: Kerry Wang & Ula Huang