Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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]
)
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
@Shak85 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIs 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!
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.
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.
@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 |
@Greg_Deckler Before you go in too deep, I just remembered that a new week should increment on Sundays and not every 8th day.
@jeronimo2334 - OK, so now I need to understand how you are getting from your source data to your intended result.
Are you saying that given an ID and timestamp and number of weeks you want to generate a row for every date from the start date timestamp for the number of weeks specified. So, if I had
ID = 1, Weeks = 1 and a timestamp that translated to 1/1/2020 I would get rows like:
ID,Date,Week
1,1/1/2020,1
1,1/2/2020,1
1,1/3/2020,1
...
1,1/7/2020,1
For example.
@Greg_DecklerPrecisely! The only caveat is that the week indicator would have to increment every Sunday to indicate that it's a new week, not every 7 days.
OK, @jeronimo2334 , now we are getting somewhere. Sample data, expected results and an explanation of how to get from point A to point B. Let me take a look.
Alright @jeronimo2334 this took some WORK!! Had to pull out a whole bag of tricks on this one. Starts with converting your Unix epoch date to UTC:
firstDate =
VAR UnixDays = [timeStamp]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)
Then this monstrosity. PBIX is attached.
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",[__Sequential]
)
@Greg_Deckler This is amazing! One thing though, the week should be just a counter, not a representation of a week on the calendar year.
In other words, the week column should not be bigger than the original weekLength at any point. For instance, if you have a row like:
id - weekLength - firstDate
23 - 5 - 1584964800
The first week should start with the week counter 1, then on the first Sunday we increment the week counter to 2 and so on until we reach week 5. The reason I'm mentioning Sunday a lot is because the firstDate timestamps could be any day of the week, so we can not programmatically increment the counter.
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, you are phenomenal! I didn't have any expectations when I started working on this but now I can deliver my project! Many thanks to you sir.
One last thing if it's not too much, would you mind adding some comments on your code snippet just so I can try to understand how this is all coming together.
OK, @jeronimo2334 so I'll walk you through the code and just FYI the code is written such that I was figuring it out along the way, it is in no way optimized or anything like that.
So I started with the premise that I was going to have to use GENERATE to take your fact table and create a Cartesian product against a date table. Any time you see DAX needing to create rows out of nothing, it's a good bet that GENERATE is going to be involved. Or GENERATESERIES.
So, the first thing to do is to get a represenetation of your table. That is __Table. Probably didn't need to use SUMMARIZE here, probably could have just used ADDCOLUMNS. Your times were in 12:00:00 PM, which does not match up with 12:00:00 AM which is the date/times created by the CALENDAR function so __DateNum1 uses INT to just return the day portion of the date/time value, stripping away the time component.
Next we need our Calendar table. I used the principle of reasonable minimums/maximums, you may have to change the range here. Added on __Weeknum column using WEEKNUM with 17 which is an undocumented DAX trick that starts a new week on Sunday. __DateNum2 is the same story as __DateNum1. Also need to add __Year because we are going to need __Sequential later on.
So, in __GeneratedTable, we can use GENERATE to create the Cartesian product of our two tables. We can then FILTER this for any rows where __DateNum2 >= __DateNum1. If __DateNum2 < __DateNum1 we don't want those rows because __DateNum1 is our "firstDate".
Now comes some fun. We need to add a sequential week identifier per ID to our __GeneratedTable and call the new table __GeneratedTable2. Sequential adds a sequential week number. Now, there may be an issue here as this version does not account for incomplete weeks at the end of the year. I did create a version that accounts for this I may have to dig it up.
So, now we can create __GeneratedTable3. In this __WeeksFromMin we find the minimum sequential value for each id and subtract that from our sequential week value and add 1. Now we have our week counter. So, in our RETURN, we simply FILTER our __GeneratedTable3 so for rows where the __WeeksFromMin is less than or equal to the desired weekLength. And we use SELECTCOLUMNS to get rid of all the unnecessary just columns we have created.
@Greg_Deckler again many thanks!
I just noticed an issue with weeks incrementing improperly when the year changes. Added a picture that shows how the week incremented on Wednesday instead of Sunday.
Yep, that's what I was referring to regarding weeks at the end of the year. It's because WEEKNUM is by year so a week that spans years is going to have some of it be 52/53 and the other part 1. I have a fix for it, let me dig it up.
OK, @jeronimo2334 I believe I have it, had to adjust it a bit for you particular circumstance. Probably need to create a Sequential 2 Quick Measure that establishes this pattern. The only changes were to the __Sequential column creation. Basically, in the MaxWeek table variable, I add a column called __Count, which counts how many days are in the last week of the year. Then I add an adjustment variable which counts how many previous years the __Count is less than 7. We can then simply adjust (subtract) that many week numbers from our calculation for __Sequential. Seems to work a treat.
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 = ADDCOLUMNS(SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum])),"__Count",COUNTROWS(FILTER(__GeneratedTable,[id]=EARLIER([id])&&[__Year]=EARLIER([__Year]) && [__Weeknum]=[MaxWeek])))
VAR MyYear = [__Year]
VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
VAR adjusment = COUNTROWS(FILTER(MaxWeeks,[__Year]<MyYear && [__Count] < 7))
VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum]-adjusment)
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]
)
@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])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |