Forum Discussion

jeronimo2334's avatar
jeronimo2334
Icon for Helper III rankHelper III
6 years ago
Solved

Calculate date table from timestamps

Hello,   I was hoping I could get some input on how to achieve this through DAX or SQL : I have a table with 3 columns: ids , lengths & timestamps. What I am trying to do is create a new table tha...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Yes, it is. 🙂 Seriously, @jeronimo2334, this was a real challenge.

    So I thought about that. I think you just have to make a very small edit:

    Table 2 = 
    VAR __Table =
        SUMMARIZE(
            'Table',
            'Table'[id],
            'Table'[weekLength],
            'Table'[firstDate],
            "__DateNum1",INT([firstDate])
        )
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(
                DATE(2019,1,1),
                DATE(2021,12,31)
            ),
            "__Weeknum",WEEKNUM([Date],17),
            "__DateNum2",INT([Date]),
            "__Year",YEAR([Date])
        )
    VAR __GeneratedTable = 
            FILTER(
                GENERATE(__Table,__Calendar),
                [__DateNum2]>=[__DateNum1]
            )
    VAR __GeneratedTable2 = 
        ADDCOLUMNS(
            __GeneratedTable,
            "__Sequential",
                VAR MaxWeeks = SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum]))
                VAR MyYear = [__Year]
                VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
                VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
                VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum])
                RETURN myNum
        )
    VAR __GeneratedTable3 = 
        ADDCOLUMNS(
            __GeneratedTable2,
            "__WeeksFromMin",
                [__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
        )
RETURN
    SELECTCOLUMNS(
        FILTER(
            __GeneratedTable3,
            [__WeeksFromMin] <= [weekLength]
        ),
        "id",[id],
        "date",[Date],
        "week",[__WeeksFromMin]
    )

jeronimo2334's avatar
jeronimo2334
Icon for Helper III rankHelper III
6 years ago

Greg_Deckler  again many thanks!

 

I just noticed an issue with weeks incrementing improperly when the year changes. Added a picture that shows how the week incremented on Wednesday instead of Sunday.

 

 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Yep, that's what I was referring to regarding weeks at the end of the year. It's because WEEKNUM is by year so a week that spans years is going to have some of it be 52/53 and the other part 1. I have a fix for it, let me dig it up.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    OK, jeronimo2334 I believe I have it, had to adjust it a bit for you particular circumstance. Probably need to create a Sequential 2 Quick Measure that establishes this pattern. The only changes were to the __Sequential column creation. Basically, in the MaxWeek table variable, I add a column called __Count, which counts how many days are in the last week of the year. Then I add an adjustment variable which counts how many previous years the __Count is less than 7. We can then simply adjust (subtract) that many week numbers from our calculation for __Sequential. Seems to work a treat.

    Table 2 = 
    VAR __Table =
        SUMMARIZE(
            'Table',
            'Table'[id],
            'Table'[weekLength],
            'Table'[firstDate],
            "__DateNum1",INT([firstDate])
        )
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(
                DATE(2019,1,1),
                DATE(2021,12,31)
            ),
            "__Weeknum",WEEKNUM([Date],17),
            "__DateNum2",INT([Date]),
            "__Year",YEAR([Date])
        )
    VAR __GeneratedTable = 
            FILTER(
                GENERATE(__Table,__Calendar),
                [__DateNum2]>=[__DateNum1]
            )
    VAR __GeneratedTable2 = 
        ADDCOLUMNS(
            __GeneratedTable,
            "__Sequential",
                VAR MaxWeeks = ADDCOLUMNS(SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum])),"__Count",COUNTROWS(FILTER(__GeneratedTable,[id]=EARLIER([id])&&[__Year]=EARLIER([__Year]) && [__Weeknum]=[MaxWeek])))
                VAR MyYear = [__Year]
                VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
                VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
                VAR adjusment = COUNTROWS(FILTER(MaxWeeks,[__Year]<MyYear && [__Count] < 7))
                VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum]-adjusment)
                RETURN myNum
        )
    VAR __GeneratedTable3 = 
        ADDCOLUMNS(
            __GeneratedTable2,
            "__WeeksFromMin",
                [__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
        )
RETURN
    SELECTCOLUMNS(
        FILTER(
            __GeneratedTable3,
            [__WeeksFromMin] <= [weekLength]
        ),
        "id",[id],
        "date",[Date],
        "week",[__WeeksFromMin]
    )