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

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's avatar
Jihwan_Kim
Icon for Super User rankSuper 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