cancel
Showing results for
Did you mean:  Helper II

## Using 1 day of data repeatedly for an entire year or years

I have 1 day of data with 24 rows, first column is the Time ranging from 0 to 23 and the other columns are average electric car charging data.

I am trying to figure out and coming short in ideas on how to use this 1 day data as a repeating everyday happening for 1 or 10 or 20 years, without having to create (24 x 365 x year) rows of data. What if I wanted to involve another table into this forecast, which has data for 1 year, but I wanted it to multiply with this table and continue producing the result for both tables multipled for 30 years ahead?

Is this too complex for power bi ?

2 ACCEPTED SOLUTIONS  Super User

If you have a relationship from Table1 to Table2 then you could use

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
ADDCOLUMNS ( 'Table', "New Col", 'Table'[Col1] * RELATED ( 'Table 2'[Col2] ) )
)
``````

If you can't have a relationship between the 2 you would need to use LOOKUPVALUE, e.g.

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
'Table',
"New Col",
VAR valFromTable2 =
LOOKUPVALUE ( 'Table 2'[Col2], 'Table 2'[Time], 'Table 1'[Time] )
RETURN
'Table'[Col1] * valFromTable2
)
)
``````  Super User

Rather than ADDCOLUMNS you could use SELECTCOLUMNS. The rest of the code would remain the same.

14 REPLIES 14  Helper II

Apparently the above solution only works for 1 table of data versus the calendar. So If i wanted to use the data from another table, it would get messy, as every row of the table gets multiplied with the entire row of the 2nd table. hmm...Maybe I should use column calculation afterward?  Super User

Adding a calculated column to the newly generated table is definitely an option, or you could add further columns to the SELECTCOLUMNS expression  Helper II

I am afraid my calculate() isnt working, and adding further columns in a selectcolumns from another table gives following error:

`Multiple values ​​were provided where only one was expected.`  Super User

When using the SELECTCOLUMNS approach, the expression will be calculated once for each row of the base table, so the expression needs to return a single scalar value.

Adding a calculated column after the initial generation is the same, the expression will be evaluated once for each row, but this time there will be more rows due to the CROSSJOIN. It still needs to provide a single scalar value for each row.  Super User

Even 30 years worth of data would be less than 300k rows, Power BI can handle that no problem at all. Do a CROSSJOIN between your existing table and a generated table, like

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
'Table'
)
``````  Helper II

Thank you for reply, I am partly agreeing that this may be the only way to go forward, due to limitations of PBI. Is there a way to use 1 single column of this "New Table" and multiplying it with all of the data of another table, lets call it table2.
Table2 has:
Time values 0-23 on first column and other columns having random numbers..

thanks alot  Super User

If you have a relationship from Table1 to Table2 then you could use

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
ADDCOLUMNS ( 'Table', "New Col", 'Table'[Col1] * RELATED ( 'Table 2'[Col2] ) )
)
``````

If you can't have a relationship between the 2 you would need to use LOOKUPVALUE, e.g.

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
'Table',
"New Col",
VAR valFromTable2 =
LOOKUPVALUE ( 'Table 2'[Col2], 'Table 2'[Time], 'Table 1'[Time] )
RETURN
'Table'[Col1] * valFromTable2
)
)
``````  Helper II

Thank you. This is much better.
If I wanted to change the Date(thisYear,12,31) into only showing 4 parameters for each month i.e. Weekday and Weekend, then I would change how?  Super User

I don't understand. What columns do you want to see in the final table, and what values do you expect them to have ?  Helper II

For example if I instead wanted to predict the electricity usage of costumers for weekdays and weekends...since the 5 day of the week is weekday and people usually behave the same in usage. and weekend, saturday and sunday, people are not working and behave otherwise.

If I instead wanted to categorize my calender into these paramters instead of 7 days a week it would be 2 days called weekday and weekend.
I saw somewhere powerbi had something inherently. Either way, thank you and ill accept your solution gracefully.  Super User

You could use ADDCOLUMNS to add extra info to the calendar before doing the CROSSJOIN, e.g.

``````New Table =
VAR thisYear =
YEAR ( TODAY () )
RETURN
CROSSJOIN (
CALENDAR ( DATE ( thisYear, 1, 1 ), DATE ( thisYear + 30, 12, 31 ) ),
"Is working day", SWITCH ( WEEKDAY ( [Date] ), 1, 0, 7, 0, 1 )
),
ADDCOLUMNS ( 'Table', "New Col", 'Table'[Col1] * RELATED ( 'Table 2'[Col2] ) )
)
``````  Helper II

Ok Thank you alot. Although I am trying to figure out how to remove the "residue" of crossjoin function, which is it taking the entire "crossjoined" table and merging it into the current table. I am only interested in the calculated column, not the entire thing. Do you have a quick fix for this? or is crossjoin not ideal here and Calculate maybe is? However I still like the calender() aproach.
thank you  Super User

Rather than ADDCOLUMNS you could use SELECTCOLUMNS. The rest of the code would remain the same.  Helper II

Thanks alot, you saved me 🙂   