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] )
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!
- jeronimo23346 years ago
Helper III
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_Deckler6 years ago
Community Champion
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.
- jeronimo23346 years ago
Helper III
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