Calculate date table from timestamps

OK, jeronimo2334 so I'll walk you through the code and just FYI the code is written such that I was figuring it out along the way, it is in no way optimized or anything like that.

So I started with the premise that I was going to have to use GENERATE to take your fact table and create a Cartesian product against a date table. Any time you see DAX needing to create rows out of nothing, it's a good bet that GENERATE is going to be involved. Or GENERATESERIES.

So, the first thing to do is to get a represenetation of your table. That is __Table. Probably didn't need to use SUMMARIZE here, probably could have just used ADDCOLUMNS. Your times were in 12:00:00 PM, which does not match up with 12:00:00 AM which is the date/times created by the CALENDAR function so __DateNum1 uses INT to just return the day portion of the date/time value, stripping away the time component.

Next we need our Calendar table. I used the principle of reasonable minimums/maximums, you may have to change the range here. Added on __Weeknum column using WEEKNUM with 17 which is an undocumented DAX trick that starts a new week on Sunday. __DateNum2 is the same story as __DateNum1. Also need to add __Year because we are going to need __Sequential later on.

So, in __GeneratedTable, we can use GENERATE to create the Cartesian product of our two tables. We can then FILTER this for any rows where __DateNum2 >= __DateNum1. If __DateNum2 < __DateNum1 we don't want those rows because __DateNum1 is our "firstDate".

Now comes some fun. We need to add a sequential week identifier per ID to our __GeneratedTable and call the new table __GeneratedTable2. Sequential adds a sequential week number. Now, there may be an issue here as this version does not account for incomplete weeks at the end of the year. I did create a version that accounts for this I may have to dig it up.

So, now we can create __GeneratedTable3. In this __WeeksFromMin we find the minimum sequential value for each id and subtract that from our sequential week value and add 1. Now we have our week counter. So, in our RETURN, we simply FILTER our __GeneratedTable3 so for rows where the __WeeksFromMin is less than or equal to the desired weekLength. And we use SELECTCOLUMNS to get rid of all the unnecessary just columns we have created.

Greg_Deckler  This is amazing! One thing though, the week should be just a counter, not a representation of a week on the calendar year.

In other words, the week column should not be bigger than the original weekLength at any point. For instance, if you have a row like:

id  - weekLength - firstDate

23 - 5 - 1584964800

The first week should start with the week counter 1, then on the first Sunday we increment the week counter to 2 and so on until we reach week 5. The reason I'm mentioning Sunday a lot is because the firstDate timestamps could be any day of the week, so we can not programmatically increment the counter.

Greg_Deckler, Awesome, that did the trick!

Greg_Deckler, you are phenomenal! I didn't have any expectations when I started working on this but now I can deliver my project! Many thanks to you sir.

One last thing if it's not too much, would you mind adding some comments on your code snippet just so I can try to understand how this is all coming together.

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.

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.

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