Forum Discussion

jeronimo2334's avatar
jeronimo2334
Icon for Helper III rankHelper III
6 years ago
Solved

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.

 

 

 

 

 

 

 

 

 

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    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]
        )

22 Replies

  • 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's avatar
      jeronimo2334
      Icon for Helper III rankHelper 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

      iddateweek
      101/23/20201
      101/24/20201
      101/25/20201
      101/26/20201
      101/27/20201
      101/28/20201
      101/29/20201
      101/30/20202
      101/31/20202
      102/01/20202
      102/02/20202
      102/03/20202
      204/12/20201
      204/13/20201
      204/14/20201
      204/15/20201
      204/16/20201
      204/17/20201
      204/18/20201
      204/19/20202

       

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion

        Try this:

        1. In Power Query, right-click on your original table and select "Reference"
        2. In that table, select the ID and timestamp columns then REMOVE OTHER COLUMNS from the Home ribbon menu.
        3. Do whatever you do to convert that timestamp to a real date. 
        4. Use the Date.AddDay([Date],-7) feature to roll it back 7 days in a new column.
        5. 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.

         

    • jeronimo2334's avatar
      jeronimo2334
      Icon for Helper III rankHelper III

      Greg_Deckler 

      I have this:

       

      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.

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity 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.

         

  • Anonymous's avatar
    Anonymous
    Not 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.

     

    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

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity 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.