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 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

 

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]
        )
    )

10 Replies

    • Ju7438's avatar
      Ju7438
      Regular Visitor
      4 years ago

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

      BR

      Julien

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
        Super 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.