Creating a list between start date and end date to corresponding days of the week

[Anonymous]

Hi all

I have the following data.  I would like to create a list of dates between start and end dates. I have tried using a List but because I have the days of the week (attribute) listed as well as the corresponding time worked (value) , I get a list dates between the two dates, 7 times. What I need to see is the date against each week day that is between the start and end dates. I suppose basically something like a fill down or formula that adds 1 day increment to each row and then on the 8th row, it starts from that date and adds 1 day increments again. 

Can anyone help me out please?

[pbi101 · Accepted solution]

Hi @KazL,

Please try this ...

Just add the Ordinal value of [Attribute] to the [Start of Week]

eg. Date.AddDays([Start of Week], List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]))

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRMtI3sNA3MjAyBLKBLBgnVidaKaQ0Fb+C8NQUAiZklOJX4FaUiV9BcGIJAQWlBHwB86ahIUjMAKIAxkH2Jk4FMG/iNgHqTZwKYN7EqQDmTdwKSvH5IhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Start of Week" = _t, #"Week Ending" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Week", type date}, {"Week Ending", type date}}),
#"Added Week Day Number" = Table.AddColumn(#"Changed Type", "Week Day Number", each List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]), Int64.Type),
#"Added Date" = Table.AddColumn(#"Added Week Day Number", "Date", each Date.AddDays([Start of Week],[Week Day Number]), type date)
in
#"Added Date"

[Anonymous]

Thanks pbi101. That worked well. Was just what I needed. 

I appreciate your time in helping me out. 

Cheers

[Samarth_18]

Hi @Anonymous ,

You can follow below article. It helped me previously for same issue:-

https://www.cloudfronts.com/how-to-list-all-dates-between-two-dates-in-powerbi-and-distribute-numbers-evenly-among-them/ 

Thanks, 

Samarth

[Anonymous]

Thanks Samarth. I have tried this but it didn't work as I get 7 lots of 7 days in the new column. This is because I have 7 rows of the same start and end date. 

Karen

[amitchandak]

@Anonymous , Something like this , a new column in M

List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1,0,0,0))

or

List.Dates([Start Date], Duration.Days([End Date] - [Start Date])+1, #duration(1,0,0,0))

[Anonymous]

That didn't work either @amitchandak 🙁 This is what I got. 

[Anonymous]

Thanks @amitchandak. I will give that a go.