Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous

Please see attached file for DAX solution

 

My apologies for late reply

 

It works with your sample data

 

paul.png


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@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"

Regards
Zubair

Please try my custom visuals

@Anonymous

 

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

it will give you an idea of the steps involved

 

Groupdatesintoranges.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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  

@Anonymous

 

I will look into it..

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad, have you managed to make any progress?

hi @Anonymous

 

So sorry....

I will look now and get back to you.

 

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

 


Regards
Zubair

Please try my custom visuals

@Anonymous

Please see attached file for DAX solution

 

My apologies for late reply

 

It works with your sample data

 

paul.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.