Trying to add rows for missing months for each group

[RickSchultz]

Hi!  Thanks for taking a look at this.

I have 2 tables.  One lists Organizations, has about 20 rows and one date field (the contract start month):

The other table is really just a list of all the "Start of month" dates from the beginning of the target period to today:

I've been trying to merge the two to get a table with a row for each month, for each Organization, from their initial contract start (from the Organization table) to today (from the date table) so I can see the revenue (MRR) for each month for each organization.  However, when I merge the two, I just get matching rows - no new rows.  I've read several responses to similar questions which all say this should work to add dates, but it seems to not work.

Any thoughts on what I'm missing, please?

[nateschultz]

Thank YOU @v-yueyunzh-msft  for your easy to follow example. I'm very new to Power BI/PQ and appreciate your help!

[wdx223_Daniel]

=let a=Table.Buffer(AnotherTable) in Table.FromRecords(List.TransformMany(Table.ToRecords(OrgTable),each Table.ToRecords(Table.SelectRows(a,(x)=>x[Start of Month]>=[contract_start])),(x,y)=>x&y&[mrr=if x[contract_start]=y[Start of Month] then x[mrr] else null]))

[v-yueyunzh-msft · Accepted solution]

Hi , @RickSchultz 

According to your description, you want to combine two tables group by [org_id]  and generate the date from 'Organization'[contract_start]  to the Max of  'Date'[Strat of Month]. Right?

You can realize it in Power Query Editor.

Here are the steps you can follow:

(1) This is my test data:

(2)We can add a new Blank Query and we put this in "Advanced Editor" :

let
    Source = Organization,
    Custom1 = Table.AddColumn(Source,"Date",(x)=> List.Select(Date[Start of Month],(y)=> y>=x[contract_start]  )    ),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"contract_start"}),
    #"Expanded test" = Table.ExpandListColumn(#"Removed Columns", "Date")
in
    #"Expanded test"

 (3)Then we can meet your need , the result is as follows:

For dax , you can create a table and enter this:

Table = 
var _t=CROSSJOIN('Organization','Date')
var _t2=FILTER(_t,[Start of Month]>=[contract_start])
return
SELECTCOLUMNS(_t2,"org_id",[org_id] , "Date" , [Start of Month])

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[wjeppesen]

This worked great for me too! What if there is a second condition? In this example, the only condition is

y>=x[contract_start]

What if you also want it to be less than contract_end? So something like this

y>=x[contract_start] && y<=x[contract_end]

[RickSchultz]

Thank you!  That's exactly what I needed.  Thank you also for the detailed explanation - my goal is to understand this stuff so I don't need to ask so many questions - you really helped me with that, too!

[jbwtp]

Sorry, what do you mean saying "new rows"? Does this mean the dates that are in the "revenue" table, but not in the "calendar" table or vise versa or both cases? This can be sorted by using a right join type outer, left or right.

[RickSchultz]

I'm sorry I wasn't clearer - thanks for the response!  

The "Revenue" table has just the first month of revenue (start month) as a date (first day of that month).  I want to add all the dates (first date of month) SINCE that time.