Group Dates into date ranges

[Anonymous]

Hi,

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

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

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

Date	Name	SeriesBoundaries	Last Date
Thursday, 01 March 2018	Mark	Series Start	Thursday, 01 March 2018
Thursday, 01 March 2018	Mark
Saturday, 03 March 2018	Mark	Series Start	Saturday, 03 March 2018
Wednesday, 07 March 2018	John	Series Start	Thursday, 08 March 2018
Thursday, 08 March 2018	John	Series End
Thursday, 15 March 2018	Steve	Series Start	Thursday, 15 March 2018
Tuesday, 20 March 2018	Mark	Series Start	Thursday, 22 March 2018
Wednesday, 21 March 2018	Mark
Thursday, 22 March 2018	Mark	Series End
Sunday, 01 April 2018	Claudia	Series Start	Sunday, 01 April 2018
Tuesday, 03 April 2018	Claudia	Series Start	Tuesday, 03 April 2018

Any help appreciated...

[Zubair_Muhammad]

@Anonymous

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"

[Zubair_Muhammad]

@Anonymous

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

it will give you an idea of the steps involved

[Anonymous]

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  

[Zubair_Muhammad]

@Anonymous

I will look into it..

[Anonymous]

@Zubair_Muhammad, have you managed to make any progress?

[Zubair_Muhammad]

hi @Anonymous

So sorry....

I will look now and get back to you.

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see attached file for DAX solution

My apologies for late reply

It works with your sample data