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 ()
    )
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 ()
    )
      • Asina's avatar
        Asina
        Icon for Helper III rankHelper III
        4 years ago

        johnt75 

        Thank you for reply.

         

        However, what is the "'Date'[Date]", what table is that?

        I assume the Data Table is where my data is, which contains data from 1-1-2021 till today?