Expand date range with repeated pattern

[Ju7438]

Hello there,

I need to do something hard in power BI and im struggling... Let me explain, i have a row with date range (start and end) that i need to expand in row for each day between, easy until now. But it getting harder cause it is a pattern with repeat until a third date. So, im looking for a tutorial to expand my range but only for the day between, and only until the repeated date.

i.e :
start date : 08/08/2022
end date : 09/08/2022
reapeat until : 31/08/2022

I want a row with the date for each monday and tuesday until the end of month, 8 rows.

Someone know how to do that ?

Thanks in advance !

Julien

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _mindate =
    MIN ( Data[Start_Date] )
VAR _maxdate =
    MAX ( Data[Repeat_Until] )
VAR _calendartable =
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "@dayname", FORMAT ( [Date], "ddd" )
    )
VAR _generatetable =
    GENERATE (
        Data,
        FILTER (
            _calendartable,
            [@dayname]
                IN { FORMAT ( Data[Start_Date], "ddd" ), FORMAT ( Data[End_Date], "ddd" ) }
                    && [Date] <= Data[Repeat_Until]
        )
    )
RETURN
    SUMMARIZE (
        _generatetable,
        Data[ID],
        Data[Start_Date],
        Data[End_Date],
        Data[Repeat_Until],
        [Date]
    )

[Ju7438]

Hi Jihwan and thanks for your sample, its good but I discover something with my datas. Due to the fact that not all row have reapeat, sometime there is date range without reapeted pattern. So it doesn't work in this case. I know how to do in QUERY but not in DAX, do you have the same calculation but in power query ?

Thanks again.

Julien

[Jihwan_Kim]

Hi,

Thank you for your message.

Sorry that I do not understand your last explanation.

If it is OK with you, could you please share your sample pbix file's link with how the expected outcome looks like? And then I can try to create a solution.

Thanks.

[Ju7438]

Sorry I didn't express my issue correctly, I have duplicate your set and add 2 more rows (id 3 and 4) :

For id 3 : I need to keep the row alone cause start date = end date, then the Date expand should chow exactly the date (27/07/2022 in this case)

For the id 4, I have a range but no repeat, then I need to expand in 3 row the range only (for the 12 / 13 / 14 of july)

Thanks again for your time !

BR

Julien

[Jihwan_Kim]

Hi,

Thank you for your explanation.

Could you please check the attached file, whether it suits your requirement?

It is for creating a new table2.

New Table 2 =
VAR _mindate =
    MIN ( Data2[Start_Date] )
VAR _maxdate =
    MAX ( Data2[Repeat_Until] )
VAR _calendartable =
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "@dayname", FORMAT ( [Date], "ddd" )
    )
VAR _generatetable =
    GENERATE (
        FILTER ( Data2, Data2[Repeat_Until] <> BLANK () ),
        FILTER (
            _calendartable,
            [@dayname]
                IN { FORMAT ( Data2[Start_Date], "ddd" ), FORMAT ( Data2[End_Date], "ddd" ) }
                    && [Date] <= Data2[Repeat_Until]
        )
    )
VAR _generatetabletwo =
    GENERATE (
        FILTER ( Data2, Data2[Repeat_Until] = BLANK () ),
        FILTER (
            _calendartable,
            [Date] >= Data2[Start_Date]
                && [Date] <= Data2[End_Date]
        )
    )
RETURN
    UNION (
        SUMMARIZE (
            _generatetable,
            Data2[ID],
            Data2[Start_Date],
            Data2[End_Date],
            Data2[Repeat_Until],
            [Date]
        ),
        SUMMARIZE (
            _generatetabletwo,
            Data2[ID],
            Data2[Start_Date],
            Data2[End_Date],
            Data2[Repeat_Until],
            [Date]
        )
    )

[Ju7438]

Hi Jihwan,

I didn't notice but there is a mistake, in your result table for id 1 and 2

It created a row from the MIN start date of all the table, but, the generated day should be between the range date until the repeated date for each id.

For i.e. the first row you generated for id 1 Date = 7/12/2022 wich is the MIN of start date id 4.

The result should start from 7/25/2022 for id 1.

I don't understand why but your expand start is false in id 1 and 2 but correct in the end of the range each time (8/30/2022 for id 1 and 8/26/2022 for id 2)

See in this image we only need the green part of table for the id 1 case :

Thank for your time, your answers are trully helping me with this manipulation !

Julien

[Jihwan_Kim · Accepted solution]

Hi,

Thank you very much for your feedback, and sorry that I did not check carefully.

Please check the attached pbix file if it suits the requirement.

New Table 2 = 
VAR _mindate =
    MIN ( Data2[Start_Date] )
VAR _maxdate =
    MAX ( Data2[Repeat_Until] )
VAR _calendartable =
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "@dayname", FORMAT ( [Date], "ddd" )
    )
VAR _generatetable =
    GENERATE (
        FILTER ( Data2, Data2[Repeat_Until] <> BLANK () ),
        FILTER (
            _calendartable,
            [@dayname]
                IN { FORMAT ( Data2[Start_Date], "ddd" ), FORMAT ( Data2[End_Date], "ddd" ) }
                    && [Date] <= Data2[Repeat_Until]
                    && [Date] >= Data2[Start_Date]
        )
    )
VAR _generatetabletwo =
    GENERATE (
        FILTER ( Data2, Data2[Repeat_Until] = BLANK () ),
        FILTER (
            _calendartable,
            [Date] >= Data2[Start_Date]
                && [Date] <= Data2[End_Date]
        )
    )
RETURN
    UNION (
        SUMMARIZE (
            _generatetable,
            Data2[ID],
            Data2[Start_Date],
            Data2[End_Date],
            Data2[Repeat_Until],
            [Date]
        ),
        SUMMARIZE (
            _generatetabletwo,
            Data2[ID],
            Data2[Start_Date],
            Data2[End_Date],
            Data2[Repeat_Until],
            [Date]
        )
    )

[Ju7438]

Thats perfectly what i need... Thank you so much you just saving me days of research, sincerely.

I accept your solution and give you a recommandation on linkedin.

Thanks again and nice to exchange again !

Julien 

[amitchandak]

@Ju7438 , Check this blog on power query can help

https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

or the attached file with dax code

[Ju7438]

Hi mate and thanks for your interest, unfortunatelly this way doesn't work in my case 😞 ..

BR

Julien