cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## 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.

1 ACCEPTED SOLUTION
Super User

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 =
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 =
__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 =
__GeneratedTable2,
"__WeeksFromMin",
[__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
)
RETURN
SELECTCOLUMNS(
FILTER(
__GeneratedTable3,
[__WeeksFromMin] <= [weekLength]
),
"id",[id],
"date",[Date],
"week",[__WeeksFromMin]
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
22 REPLIES 22
Helper I

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

Super User

@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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

Sample data as text!

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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

Helper III

@Greg_Deckler  Before you go in too deep, I just remembered that a new week should increment on Sundays and not every 8th day.

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

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 =
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 =
__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 =
__GeneratedTable2,
"__WeeksFromMin",
[__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
)
RETURN
SELECTCOLUMNS(
FILTER(
__GeneratedTable3,
[__WeeksFromMin] <= [weekLength]
),
"id",[id],
"date",[Date],
"week",[__Sequential]
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@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.

Super User

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 =
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 =
__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 =
__GeneratedTable2,
"__WeeksFromMin",
[__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
)
RETURN
SELECTCOLUMNS(
FILTER(
__GeneratedTable3,
[__WeeksFromMin] <= [weekLength]
),
"id",[id],
"date",[Date],
"week",[__WeeksFromMin]
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

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 =
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 =
__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))
RETURN myNum
)
VAR __GeneratedTable3 =
__GeneratedTable2,
"__WeeksFromMin",
[__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
)
RETURN
SELECTCOLUMNS(
FILTER(
__GeneratedTable3,
[__WeeksFromMin] <= [weekLength]
),
"id",[id],
"date",[Date],
"week",[__WeeksFromMin]
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@Greg_Deckler, Awesome, that did the trick!

Super User

@jeronimo2334 , This should be in second or millisecond. You can create new columns like

WeekNum = weeknum ([Date])

Weekday = weekday([date])

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors