Forum Discussion
jeronimo2334
Helper III
6 years agoCalculate 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...
- 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] )
Greg_Deckler
Community Champion
6 years agoRight, 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
Helper III
6 years agoGreg_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 |