create table with date range for each item

[elsys1]

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

[Anonymous · Accepted solution]

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.

[Thennarasu_R]

Hi @elsys1 

For your scenarios, you can use fill-down with formula,try this one you can able get the out put

New 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 R