Forum Discussion
Calculate date table from timestamps
- 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] )
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.
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 |
- jeronimo23346 years ago
Helper III
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_Deckler6 years ago
Community Champion
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.
- jeronimo23346 years ago
Helper III
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.