Forum Discussion

Asina's avatar
Asina
Icon for Helper III rankHelper III
3 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 to 2050, although new data will roll in after every update (daily).

For this I want to use the sample data to be reapted from today and upto 2050, by using the year to date data + the missing months i.e. from 14-09-2022 till 31-12-2022 data which is empty space, filled by using data from last year of the same time. 

 

As for 01-01-2023 and every year ahead we can just use data from the year before i.e. data for 2023 would the data from 2022, and the data for 2024 would be the new generated data from 2023 etc.

The above columns in the table shown in the picture must follow along with the projection.

Apprently, I believe it can be done with a new table, But i have had little success, using the following method:

New table =
VAR Startyear= YEAR (DATE(2021,01,01))
RETURN CALENDAR ( DATE ( Startyear, 1, 1 ), DATE ( Startyear+ 29, 12, 31 ) )

 

The above code only creates dates upto 2050 with 00.00.00 time values.

I wonder if johnt75 can help me with this 🙂

General Comment
Need Help
  • johnt75's avatar
    johnt75
    3 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 ()
    )

14 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 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
      3 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
        3 years ago

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