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 tha...
Need Help
  • 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]
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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
    6 years ago

    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
      6 years ago

      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.