Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeronimo2334
Helper III
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.

 

 

Capture32312312123123.JPG

 

 

 

 

 

 

 

 

 

1 ACCEPTED 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]
    )


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

22 REPLIES 22
Shak85
Helper I
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.

 

DateTime PeriodTime range
01/04/2020WeeklyCurrent week (05-04-2020)
  W/e 29/03/20220
  w/e 22/03/2020
  w/e 15/03/2020
 MonthlyCurrent month (April)
  March
  February
  January
 QuarterlyCurrent 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. 



Did I answer your question? Mark my post as a solution!
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
Greg_Deckler
Super User
Super User

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!

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

I have this:

Capture32312312123123.JPG

 

I want this:

 

iddateweek
325872/5/20201
325872/6/20201
325872/7/20201
325872/8/20201
325872/9/20201
325872/10/20201
325872/11/20201
325872/12/20202
325872/13/20202
325872/14/20202
325872/15/20202
325872/16/20202
325872/17/20202
325872/18/20202
325872/19/20203
325872/20/20203
325872/21/20203
325872/22/20203
325872/23/20203
325872/24/20203
325872/25/20203
930245/12/20201
930245/13/20201
930245/14/20201
930245/15/20201
930245/16/20201
930245/17/20201
930245/18/20201
953792/22/20191
953792/23/20191
953792/24/20191
953792/25/20191
953792/26/20191
953792/27/20191
953792/28/20191
953793/1/20192
953793/2/20192
953793/3/20192
953793/4/20192
953793/5/20192
953793/6/20192
953793/7/20192

 

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I didn't know you were doing that, appreciated it!

Original table:

idweekLengthtimeStamp
84893141575892800
8684821582459200
89665151575288000
8966891584964800
90115161575288000
90262171579435200
90449271573905600
90532111583755200
90556161575288000
90581161575288000
90635121578312000
90701161575288000
90723181575201600
90771201578312000
91571151575288000
91672151575288000
91708181575892800
91724121577966400
91894181574683200

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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]
    )

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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]
    )


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

Capture33221212112.JPG

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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]
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler, Awesome, that did the trick!

amitchandak
Super User
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])

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.