Forum Discussion
Calculate 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 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.
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] )
22 Replies
- amitchandak
Super User
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])
- jeronimo2334
Helper III
amitchandakYou misunderstood, I don't want new columns. I need a new table that takes each timestamp from the original table and auto increments in a date format depending on the number of length * 7.
eg
id date week 1 01/23/2020 1 1 01/24/2020 1 1 01/25/2020 1 1 01/26/2020 1 1 01/27/2020 1 1 01/28/2020 1 1 01/29/2020 1 1 01/30/2020 2 1 01/31/2020 2 1 02/01/2020 2 1 02/02/2020 2 1 02/03/2020 2 2 04/12/2020 1 2 04/13/2020 1 2 04/14/2020 1 2 04/15/2020 1 2 04/16/2020 1 2 04/17/2020 1 2 04/18/2020 1 2 04/19/2020 2 - edhans
Community Champion
Try this:
- In Power Query, right-click on your original table and select "Reference"
- In that table, select the ID and timestamp columns then REMOVE OTHER COLUMNS from the Home ribbon menu.
- Do whatever you do to convert that timestamp to a real date.
- Use the Date.AddDay([Date],-7) feature to roll it back 7 days in a new column.
- You can then click on that new column, select the "Add Columns" menu, then Date, Week, Week Number to add a week number column. Week of year or month, whatever you need.
- Greg_Deckler
Community Champion
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
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_Deckler
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.
- AnonymousNot applicable
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
Community Champion
Anonymous 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.