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
Icon for Super User rankSuper User
4 years ago

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

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

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

        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