Forum Discussion
Helper IIIRepeat sample year data upto 2050
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 () )
Super UserYou 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.
AsinaHi 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