Forum Discussion

Asina's avatar
Asina
Icon for Helper III rankHelper III
4 years ago
Solved

Repeat sample year data upto 2050

I have a set of data which has Date values for every hour from 2021 and 2022 with new data coming hourly after each update. example table named: Sampletable   I want to extend the table data...
General Comment
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You can create the data table with fake datestamps directly from your existing data table with

    Fake Data Table =
VAR StartDate =
    CALCULATE (
        MINX ( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, YEAR ), 'Date'[Date] )
    )
RETURN
    CALCULATETABLE (
        ADDCOLUMNS (
            'Data Table',
            "Fake Datestamp",
                DATE ( 1900, MONTH ( 'Data Table'[Datestamp] ), DAY ( 'Data Table'[Datestamp] ) )
                    + TIME ( HOUR ( 'Data Table'[Datestamp] ), MINUTE ( 'Data Table'[Datestamp] ), 0 )
        ),
        'Data Table'[Datestamp] >= StartDate
            && 'Data Table'[Datestamp] < TODAY ()
    )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could create a new table with a full year's worth of data in it, start dates and end dates determined by the last date you have data for. Add a column to this new table called Fake Timestamp which has the same datetime but in the year 1900.

Create a second table like

baseCalendar = ADDCOLUMNS(
    GENERATE(
        ADDCOLUMNS(
            CALENDAR( date(2022, 9, 1), date(2050,12,31) ),
            "Month num", MONTH([Date]),
            "Day of month", DAY([Date])
        ),
        SELECTCOLUMNS(
            GENERATESERIES( 0, 23, 1),
            "Hour", [Value]
        )
    ),
    "Real timestamp", [Date] + TIME([Hour], 0, 0),
    "Fake timestamp", DATE( 1900, [Month num], [Day of month]) + TIME( [Hour], 0, 0) 
)

and then link the two tables on the Fake Timestamp column. You can then create new columns in this calendar table pulling data from the info table using RELATED.

Asina's avatar
Asina
Icon for Helper III rankHelper III
4 years ago

johnt75 

Hi and thank you for your reply.

I have done as you guided, created a new table with date from 1-1-2021 till 31-12-2021, although I am interested in data from 1-1-2022 till current date and fill the remaining data (days ahead till end of year) by pulling it from 2021. 

I tried to add a new column called Fake timestamp = Calendar(Date(1900,1,1),Date(1900,12,31)), however I am getting error about "Multiple values ​​were provided where only one was expected." 

 

I would like to point out that I already have a table with values and date as shown in the picture i attached to this post at the top. The table containts date values 1-1-2021 til current date. How does that come to play in this situation, since it contains all the data and info?

thanks

  • Asina's avatar
    Asina
    Icon for Helper III rankHelper III
    4 years ago

    however this works for Fake timestamp = if(year(KalenderX[Date]) <> 1900,DATE(1900,MONTH(KalenderX[Date]),DAY(KalenderX[Date])))

    • Asina's avatar
      Asina
      Icon for Helper III rankHelper III
      4 years ago
      An argument to the 'DATE' function has the wrong data type, or the result is too large or too small.

      Is what I get when i add baseCalendar

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        You can create the data table with fake datestamps directly from your existing data table with

        Fake Data Table =
VAR StartDate =
    CALCULATE (
        MINX ( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, YEAR ), 'Date'[Date] )
    )
RETURN
    CALCULATETABLE (
        ADDCOLUMNS (
            'Data Table',
            "Fake Datestamp",
                DATE ( 1900, MONTH ( 'Data Table'[Datestamp] ), DAY ( 'Data Table'[Datestamp] ) )
                    + TIME ( HOUR ( 'Data Table'[Datestamp] ), MINUTE ( 'Data Table'[Datestamp] ), 0 )
        ),
        'Data Table'[Datestamp] >= StartDate
            && 'Data Table'[Datestamp] < TODAY ()
    )