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] )
Anonymous
6 years agoNot 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
6 years agoAnonymous 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.