Calculate date table from timestamps

[jeronimo2334]

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 that has id , week number , date. To calculate the new table we take the timestamp from the first table and add days equal to the number of length multiplied by 7 because each length is a week. The week column starts at 1 by default and then increments depending on the number of length.

I know, it's a little confusing, let me know if I should provide more information.

[Shak85]

Hi all, I want to create a date table in following format. If we select Weekly then all the date shows with week ending on the date except the latest week with "current week" same for months quarter and year.

Date	Time Period	Time range
01/04/2020	Weekly	Current week (05-04-2020)
		W/e 29/03/20220
		w/e 22/03/2020
		w/e 15/03/2020
	Monthly	Current month (April)
		March
		February
		January
	Quarterly	Current quarter (Q1 2020)
		Q4 2019
		Q3 2019

Thanks

[edhans]

@Shak85 please create a new thread for your question. This thread has been marked as solved, and it cannot have two solutions to two different problems. 

[Greg_Deckler]

Is that the source table or the desired table? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Sample data as text!

[jeronimo2334]

@Greg_Deckler 

I have this:

I want this:

id	date	week
32587	2/5/2020	1
32587	2/6/2020	1
32587	2/7/2020	1
32587	2/8/2020	1
32587	2/9/2020	1
32587	2/10/2020	1
32587	2/11/2020	1
32587	2/12/2020	2
32587	2/13/2020	2
32587	2/14/2020	2
32587	2/15/2020	2
32587	2/16/2020	2
32587	2/17/2020	2
32587	2/18/2020	2
32587	2/19/2020	3
32587	2/20/2020	3
32587	2/21/2020	3
32587	2/22/2020	3
32587	2/23/2020	3
32587	2/24/2020	3
32587	2/25/2020	3
93024	5/12/2020	1
93024	5/13/2020	1
93024	5/14/2020	1
93024	5/15/2020	1
93024	5/16/2020	1
93024	5/17/2020	1
93024	5/18/2020	1
95379	2/22/2019	1
95379	2/23/2019	1
95379	2/24/2019	1
95379	2/25/2019	1
95379	2/26/2019	1
95379	2/27/2019	1
95379	2/28/2019	1
95379	3/1/2019	2
95379	3/2/2019	2
95379	3/3/2019	2
95379	3/4/2019	2
95379	3/5/2019	2
95379	3/6/2019	2
95379	3/7/2019	2

Basically every Id on the original table is an index for the new table. Each timestamp in the original table is used to create the initial date entry and then it has to auto increment dates depending on the number of lengths from the original table times 7 since length is a week.

[Greg_Deckler]

Right, @jeronimo2334 paste source as text just like you pasted the result you want. Otherwise, I have to type all of your data in to test and I am not going to do that.

[jeronimo2334]

@Greg_Deckler  I didn't know you were doing that, appreciated it!

Original table:

id	weekLength	timeStamp
84893	14	1575892800
86848	2	1582459200
89665	15	1575288000
89668	9	1584964800
90115	16	1575288000
90262	17	1579435200
90449	27	1573905600
90532	11	1583755200
90556	16	1575288000
90581	16	1575288000
90635	12	1578312000
90701	16	1575288000
90723	18	1575201600
90771	20	1578312000
91571	15	1575288000
91672	15	1575288000
91708	18	1575892800
91724	12	1577966400
91894	18	1574683200

[jeronimo2334]

@Greg_Deckler  Before you go in too deep, I just remembered that a new week should increment on Sundays and not every 8th day.

[Greg_Deckler]

@jeronimo2334  - OK, so now I need to understand how you are getting from your source data to your intended result.

Are you saying that given an ID and timestamp and number of weeks you want to generate a row for every date from the start date timestamp for the number of weeks specified. So, if I had

ID = 1, Weeks = 1 and a timestamp that translated to 1/1/2020 I would get rows like:

ID,Date,Week

1,1/1/2020,1

1,1/2/2020,1

1,1/3/2020,1

...

1,1/7/2020,1

For example.

[jeronimo2334]

@Greg_DecklerPrecisely! The only caveat is that the week indicator would have to increment every Sunday to indicate that it's a new week, not every 7 days.

[Greg_Deckler]

OK, @jeronimo2334 , now we are getting somewhere. Sample data, expected results and an explanation of how to get from point A to point B. Let me take a look.

[Greg_Deckler]

Alright @jeronimo2334 this took some WORK!! Had to pull out a whole bag of tricks on this one. Starts with converting your Unix epoch date to UTC:

firstDate = 
VAR UnixDays = [timeStamp]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

Then this monstrosity. PBIX is attached.

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",[__Sequential]
    )

[jeronimo2334]

@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 · Accepted solution]

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]

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

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.

[jeronimo2334]

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

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]

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

[jeronimo2334]

@Greg_Deckler, Awesome, that did the trick!

[amitchandak]

@jeronimo2334 , This should be in second or millisecond. You can create new columns like

Date = dateadd(date(1900,1,1),[firstdate]/(24*60*60),DAY)

WeekNum = weeknum ([Date])

Weekday = weekday([date])