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...
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 ()
    )
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])))

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

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

        Yes, the Data Table is where you have data from 2021 to date. 

        The 'Date' table is your model's date or calendar table. It should have 1 entry for each day for each year you wish to show data for and it should be marked as a date table in Power BI. Almost every model should have one