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:

 

Vlianlmsft_0-1650509075691.png

 

Expected Results:

Vlianlmsft_1-1650509075703.png

 

 

Method 1Use 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)

 

 

        

Vlianlmsft_2-1650509075712.png

 

  1. 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

Vlianlmsft_3-1650509075717.png

 

 

Method 2: Use Power Query to group consecutive dates.

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

Table Date1 — Index ColumnFrom 0.

Vlianlmsft_4-1650509075720.png

 

  1. Click on Add ColumnCustom Column.

Enter the following code:

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

Vlianlmsft_5-1650509075723.png

 

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

Vlianlmsft_6-1650509075724.png

 

Replace it with null.

Vlianlmsft_7-1650509075725.png

 

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

Vlianlmsft_8-1650509075728.png

 

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

Vlianlmsft_9-1650509075728.png

 

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

Vlianlmsft_10-1650509075730.png

 

Vlianlmsft_11-1650509075732.png

 

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

Vlianlmsft_12-1650509075735.png

 

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

Vlianlmsft_13-1650509075737.png

 

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

Vlianlmsft_14-1650509075740.png

 

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

Vlianlmsft_15-1650509075741.png

 

        

Vlianlmsft_16-1650509075744.png

 

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

Vlianlmsft_17-1650509075745.png

 

Vlianlmsft_18-1650509075748.png

 

  1. Delete redundant columns.

Vlianlmsft_19-1650509075749.png

 

 

Result

Vlianlmsft_20-1650509075750.png

 

 

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

Group by consecutive dates.pbix



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*