topic Creating a list between start date and end date to corresponding days of the week in DAX Commands and Tips

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

Anonymous — Mon, 23 Aug 2021 08:39:55 GMT

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?

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

Samarth_18 — Mon, 23 Aug 2021 09:12:09 GMT

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

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

Anonymous — Mon, 23 Aug 2021 09:19:42 GMT

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

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

amitchandak — Mon, 23 Aug 2021 09:46:03 GMT

Anonymous , Something like this , a new column in M

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

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

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

Anonymous — Tue, 24 Aug 2021 02:35:41 GMT

Thanks amitchandak. I will give that a go.

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

Anonymous — Sun, 29 Aug 2021 22:56:21 GMT

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

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

pbi101 — Tue, 31 Aug 2021 02:50:18 GMT

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"

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

Anonymous — Fri, 03 Sep 2021 06:53:03 GMT

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

I appreciate your time in helping me out.

Cheers