Generate Table with dates and ID from another table

[Anonymous]

Hello! 

I have a table called 'Organizations' where the primary key is the 'Organization_ID'. I also have a dates table that is using the calendar function.

I would like to generate a table that has a row for every Month in my Dates table for every Organization_Id in my org table. 

Sample of my Organization_Table:

 Sample of my Dates Table:

Desired Outcome:

The reason I need a month row for every organization is because I plan on relating a table to this table that I will use to bring in some monthly totals.

How would I accomplish this? 

[AlB]

Hi @Anonymous

Do you want the cartesian product of Month and Organization_ID?

[Anonymous]

Hi @AlB ,

I apologize, but I do not know what a carterian product is.

I believe I came up with a solution that works since posting the question, though. I created two tables. The first table is called ORGID and is just a SUMMARIZE function of the OrganizationID: 

OrgID = SUMMARIZE(Organizations, Organizations[Id])

I then created another dates table as a variable and return just the months:

2018 = 
var FullCalendar =  
ADDCOLUMNS (
CALENDAR (DATE(2018,01,01), DATE(2018,12,31)),
"JoinDate", FORMAT([Date], "MM-DD-YYYY"),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"MonthYear", FORMAT ( [Date], "MM-YYYY" ),
"MonthYearShort", FORMAT ( [Date], "mmm-YYYY" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"QuarterNumber", FORMAT([Date], "Q"),
"QuarterYear", "Q" & FORMAT ( [Date], "Q") & "-" & FORMAT ( [Date], "YYYY" )
)

return 
SUMMARIZE(FullCalendar,[Year], [MonthYear], [Monthnumber], [MonthNameShort], [MonthYearShort])

I then used GenerateALL to combine them, and ended up with a table that has month per row for every organizationID:

[AlB · Accepted solution]

@Anonymous

Sounds good. You could also do

Table=CROSSJOIN(ALL(Organizations[Id]); ALL('Date'[MonthYear]))