cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KazL
Helper I
Helper I

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

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?

 

dates.png

1 ACCEPTED SOLUTION
pbi101
Frequent Visitor

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"

 

 

pbi101_0-1630319541342.png

 

View solution in original post

7 REPLIES 7
pbi101
Frequent Visitor

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"

 

 

pbi101_0-1630319541342.png

 

View solution in original post

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

I appreciate your time in helping me out. 

Cheers

Samarth_18
Memorable Member
Memorable Member

Hi @KazL ,

 

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-number... 

 

Thanks, 

Samarth

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

@KazL , 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))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

list dates.png

Thanks @amitchandak. I will give that a go. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors