Forum Discussion
Asina
Helper III
Helper IIIRepeat 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...
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 () )
AsinaHelper III
Helper IIIAn 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
Super User
Super UserYou 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
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
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