Forum Discussion
Solved
Dates between create columns
Hi guys, i need some help
I have a table with Start Date and End Date for each employee
I need to create one row for each day between these two date, repeating the employee names.
Is there a way to do that?
I had a similar usecase within my work environment and I solved this using M powerquery.
They idea is to create a list with dates between the start data and the enddate
You can create a new column containing the list with dates as followed:
Table.AddColumn( #"PreviousStepName", "NewColumnName", each List.Dates([Startdate], Duration.Days([Enddate] - [startdate]), #duration(1,0,0,0)))With this code you get a new column containing for each row a list with dates between the startdate and enddate. In the next step you simply expand the list and as a result you get a row for each date between the startdate and enddate.For example when the startdate is 1-1-2023 and the enddate is 10-1-2023 you get a row for each day.- In this case, the last day isnt beeing created, what parameter defines that?
5 Replies
You could create a calculated table in DAX
Employee dates = SELECTCOLUMNS ( GENERATE ( Employee, CALENDAR ( Employee[Start date], Employee[End date] ) ), "Employee first name", Employee[First name], "Employee last name", Employee[Last name], "Date", [Date] )I had a similar usecase within my work environment and I solved this using M powerquery.
They idea is to create a list with dates between the start data and the enddate
You can create a new column containing the list with dates as followed:
Table.AddColumn( #"PreviousStepName", "NewColumnName", each List.Dates([Startdate], Duration.Days([Enddate] - [startdate]), #duration(1,0,0,0)))With this code you get a new column containing for each row a list with dates between the startdate and enddate. In the next step you simply expand the list and as a result you get a row for each date between the startdate and enddate.For example when the startdate is 1-1-2023 and the enddate is 10-1-2023 you get a row for each day.
