Forum Discussion

Ju7438's avatar
Ju7438
Regular Visitor
4 years ago
Solved

Expand date range with repeated pattern

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 unti...
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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]
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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's avatar
Ju7438
Regular Visitor
4 years ago

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