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 agoIs 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
Helper III
6 years agoI 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.