cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paul_PowerBI Regular Visitor
Regular Visitor

Group Dates into date ranges

Hi,

 

I need to group a series of dates into date ranges. My data is as follows:

 

DateName
Thursday, 01 March 2018Mark
Thursday, 01 March 2018Mark
Saturday, 03 March 2018Mark
Wednesday, 07 March 2018John
Thursday, 08 March 2018John
Thursday, 15 March 2018Steve
Tuesday, 20 March 2018Mark
Wednesday, 21 March 2018Mark
Thursday, 22 March 2018Mark
Sunday, 01 April 2018Claudia
Tuesday, 03 April 2018Claudia

 

I need to determine the start and end dates for that episode for each name. Allong the lines of this:

 

DateNameSeriesBoundariesLast Date
Thursday, 01 March 2018MarkSeries StartThursday, 01 March 2018
Thursday, 01 March 2018Mark  
Saturday, 03 March 2018MarkSeries StartSaturday, 03 March 2018
Wednesday, 07 March 2018JohnSeries StartThursday, 08 March 2018
Thursday, 08 March 2018JohnSeries End 
Thursday, 15 March 2018SteveSeries StartThursday, 15 March 2018
Tuesday, 20 March 2018MarkSeries StartThursday, 22 March 2018
Wednesday, 21 March 2018Mark  
Thursday, 22 March 2018MarkSeries End 
Sunday, 01 April 2018ClaudiaSeries StartSunday, 01 April 2018
Tuesday, 03 April 2018ClaudiaSeries StartTuesday, 03 April 2018

 

Any help appreciated...

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Group Dates into date ranges

@Paul_PowerBI

Please see attached file for DAX solution

 

My apologies for late reply

 

It works with your sample data

 

paul.png

7 REPLIES 7
Super User
Super User

Re: Group Dates into date ranges

@Paul_PowerBI

 

Try this Power Query Solution

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskoLSpOSazUUTAwVPBNLErOUDAyMLRQ0lECcrKBFC4FsTpEaFYAKwtOLCktgigzxqoMlwKQ5vDUlLxUqCXmqLq98jPyUF1ogceFFlg1K6ApMzRFVRZcklqWimIJqgqw7lKoA40MCAShkREe7xnhC0NcZqAHdWkeLD4cC4oyc2CKnHMSS1MyE0EewqoCxRvASMChGYeS2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, #"Last Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Last Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"ALL", each _, type table}},GroupKind.Local),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Date", "SeriesBoundaries", "Last Date"}, {"Date", "SeriesBoundaries", "Last Date"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ALL", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let pd=
let
myindex=[Index],
myname=[Name]
in
List.Max(
Table.SelectRows(#"Added Index",each [Index]=myindex-1 and [Name]=myname)[Date])
in
List.AnyTrue({pd=null,pd=[Date],pd=Date.AddDays([Date],-1)})),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Name", "Custom", "Date", "SeriesBoundaries", "Last Date", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Reordered Columns", {"Name", "Custom"}, {{"ALL", each Table.AddIndexColumn(_,"IndexF",1,1), type table}},GroupKind.Local),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "LastDate", each List.Max([ALL][Date])),
    #"Expanded ALL1" = Table.ExpandTableColumn(#"Added Custom1", "ALL", {"Date", "IndexF"}, {"Date", "IndexF"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded ALL1", "Custom.1", each if [IndexF]=1 then [LastDate] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "IndexF", "LastDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "LastDate"}})
in
    #"Renamed Columns"
Super User
Super User

Re: Group Dates into date ranges

@Paul_PowerBI

 

Please see the attached file and follow the steps from Query Editor

it will give you an idea of the steps involved

 

Groupdatesintoranges.png

Paul_PowerBI Regular Visitor
Regular Visitor

Re: Group Dates into date ranges

Hi @Zubair_Muhammad,

 

Are you able to provide a DAX solution? Similar to your answer here:

 

Your Answer to a previous question

 

Except catering for multiple transactions on a single day.. You will see that I have also asked you this question under your previous solution.

 

Thanks  

Super User
Super User

Re: Group Dates into date ranges

@Paul_PowerBI

 

I will look into it..

 

Paul_PowerBI Regular Visitor
Regular Visitor

Re: Group Dates into date ranges

@Zubair_Muhammad, have you managed to make any progress?

Super User
Super User

Re: Group Dates into date ranges

hi @Paul_PowerBI

 

So sorry....

I will look now and get back to you.

 

Can we use an index column as support for DAX solution?

 

Super User
Super User

Re: Group Dates into date ranges

@Paul_PowerBI

Please see attached file for DAX solution

 

My apologies for late reply

 

It works with your sample data

 

paul.png