Create a table with row per asset per month

Question

I am trying to dynamically generate a table from a list of assets and a calendar table to create a table with a row per unique asset ID per month.

I have my Date table generated in PBI with the calendar function, with a single Date column, and a month/year column to extract that from each date.

I also have an asset list table that has a row per asset, with three columns that combined create a uniqu asset list.

ID1 Field | ID2 Field | Customer Field

001 | EAST | Cust1

003 | EAST | Cust 1

002 | WEST | Cust 4

I'd like to create a table that has a single row for each month from my calendar table, for each asset in the list.

ID1 Field | ID2 Field | Customer FIeld | M-Y Field

001 | EAST | Cust1 | Jan-19

001 | EAST | Cust 1 | Feb-19

001 | EAST | Cust1 | Mar-19

002 | WEST | Cust4 | Jan-19

002 | WEST | Cust4 | Feb-19....

mahoneypat · Accepted Answer

You can create a new calculated table with this expression

New Table = CROSSJOIN(VALUES('Date'[M-Y]), Asset) //replace with your Asset table name and M-Y column as needed

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Forum Discussion