List of dates from range and schedule

[Anonymous]

I have a table of Deals with following fields:

• period_start = DATE
• period_end = DATE
• schedule = Number of Months

Now I need to create the table of invoices, with the date column:

• if schedule is "monthly", I want to get one column with period_start date, and following rows with dates incremented by 1 month but not exceeding period_end date

[v-shex-msft · Accepted solution]

HI @Anonymous,

It is possible to generate a table with specific increase date, but it is impossible to make it dynamic based on slicer or filters.

Sample:

Expand =
VAR _start =
    DATE ( 2015, 1, 1 )
VAR _end =
    TODAY ()
VAR _calendar =
    CALENDAR ( _start, _end )
VAR temp =
    SELECTCOLUMNS (
        { ( DATE ( 2015, 1, 1 ), TODAY () ) },
        "StartDate", [Value1],
        "EndDate", [Value2]
    )
VAR interval = 2
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ADDCOLUMNS (
                    CROSSJOIN ( temp, _calendar ),
                    "Rolling Date", DATE ( YEAR ( [StartDate] ), MONTH ( [StartDate] )
                        + interval * DATEDIFF ( [StartDate], [Date], MONTH ), DAY ( [StartDate] ) )
                ),
                [Rolling Date] <= [EndDate]
            ),
            "Start Date", [StartDate],
            "End Date", [EndDate],
            "Rolling Date", [Rolling Date]
        )
    )

Notice: Bold part is modicatable.

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

HI @Anonymous,

It is possible to generate a table with specific increase date, but it is impossible to make it dynamic based on slicer or filters.

Sample:

Expand =
VAR _start =
    DATE ( 2015, 1, 1 )
VAR _end =
    TODAY ()
VAR _calendar =
    CALENDAR ( _start, _end )
VAR temp =
    SELECTCOLUMNS (
        { ( DATE ( 2015, 1, 1 ), TODAY () ) },
        "StartDate", [Value1],
        "EndDate", [Value2]
    )
VAR interval = 2
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ADDCOLUMNS (
                    CROSSJOIN ( temp, _calendar ),
                    "Rolling Date", DATE ( YEAR ( [StartDate] ), MONTH ( [StartDate] )
                        + interval * DATEDIFF ( [StartDate], [Date], MONTH ), DAY ( [StartDate] ) )
                ),
                [Rolling Date] <= [EndDate]
            ),
            "Start Date", [StartDate],
            "End Date", [EndDate],
            "Rolling Date", [Rolling Date]
        )
    )

Notice: Bold part is modicatable.

Regards,

Xiaoxin Sheng

[ImkeF · Accepted solution]

You could also do this in the query editor: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-...

Copy the code into the advanced editor of an empty query and name it "fnDatesBetween".

Then add a column to your table where you call the function and reference the relevant columns like so:

fnDatesBetween([StartDate], [EndDate], "Month")