cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asina
Helper II
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

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 ) ),
        ADDCOLUMNS (
            'Table',
            "New Col",
                VAR valFromTable2 =
                    LOOKUPVALUE ( 'Table 2'[Col2], 'Table 2'[Time], 'Table 1'[Time] )
                RETURN
                    'Table'[Col1] * valFromTable2
        )
    )

View solution in original post

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

View solution in original post

14 REPLIES 14
Asina
Helper II
Helper II

@johnt75 

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?

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

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.

 

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.

johnt75
Super User
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'
    )

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

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 ) ),
        ADDCOLUMNS (
            'Table',
            "New Col",
                VAR valFromTable2 =
                    LOOKUPVALUE ( 'Table 2'[Col2], 'Table 2'[Time], 'Table 1'[Time] )
                RETURN
                    'Table'[Col1] * valFromTable2
        )
    )

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?

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

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. 

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

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

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

Thanks alot, you saved me 🙂

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors