Forum Discussion
create table with date range for each item
Hello.
I have a table with items and data range, and I need to create a table in dax that contains dates by month for every item in fist table
The first table looks like it:
| Item | Start Date | End Date |
| Item1 | 01.01.2015 | 29.07.2024 |
| Item2 | 26.03.2020 | 02.04.2029 |
| Item3 | 13.01.2021 | 03.04.2026 |
And I need a table which looks like it:
| Item | Date |
| Item 1 | 01.2015 |
| Item 1 | 02.2015 |
| Item 1 | 03.2015 |
| Item 1 | … |
| Item 1 | 07.2024 |
| Item 2 | 03.2020 |
| Item 2 | … |
| Item 2 | 04.2029 |
Thanks for any help
Hi elsys1 ,
Based on my testing, please try the following methods:
1.Create the sample table.
2.Open the power query editor, select add custom column.
3.Enter the following formula.
List.Select( List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date]),#duration(1,0,0,0)), each Date.Day(_)=1)4.Select ok and expand the rows. The result is shown below.
5.Right click start date and end date column, select remove.
You can also view the following link to learn more information.
Solved: Re: Spread revenue across period based on start an... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi elsys1
For your scenarios, you can use fill-down with formula,try this one you can able get the out putNew Value = VAR LastNonBlankDate = CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ), FILTER ( ALL ( TableName ), TableName[Date] <= EARLIER ( TableName[Date] ) && NOT ( ISBLANK ( TableName[Value] ) ) ) ) RETURN CALCULATE ( SUM ( TableName[Value] ), FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate ) )Thanks,
Thennarasu RHi elsys1 ,
Based on my testing, please try the following methods:
1.Create the sample table.
2.Open the power query editor, select add custom column.
3.Enter the following formula.
List.Select( List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date]),#duration(1,0,0,0)), each Date.Day(_)=1)4.Select ok and expand the rows. The result is shown below.
5.Right click start date and end date column, select remove.
You can also view the following link to learn more information.
Solved: Re: Spread revenue across period based on start an... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
