Calculate orders for months open only

Try this chrisazimmerman  - see my PBIX because I had to do most of the modeling in Power Query for this to work, and then did this measure:

 

 

Order Count = 
VAR varMaxOwnerMonth = 
    CALCULATE(
        MAXX(
            'Owner Months',
            'Owner Months'[MonthCount]
        ),
        REMOVEFILTERS(Months[Month])
    )
VAR varResult = 
    IF(
        MAX(Months[Month]) <= varMaxOwnerMonth,
        COALESCE(COUNTROWS(Orders), 0),
        BLANK()
    )
RETURN
    varResult

 

 

I created a table that referenced the Owners table and called it Owners Months. This table has 1 record for each desired month. Here are the first few rows. You can look at the M code to see exactly how it works, but the code for the row inquestion is below. 99% sure someone can simplify that, but this was my first non-optimiziation shot:

 

 

    #"Added MonthCount" = 
        Table.AddColumn(
            Source, 
            "MonthCount", 
            each 
                let
                    varMonths =
                        List.Count(
                            List.Distinct(
                                List.Transform(
                                    List.Transform(
                                        {
                                            Number.From(
                                                Date.StartOfMonth([OpenDate])
                                            )..
                                            Number.From(
                                                Date.EndOfMonth(
                                                    Date.AddMonths([OpenDate], [CurrentMonth])
                                                )
                                            )
                                        },
                                        each Date.From(_)
                                    ),
                                    each Date.Year(_) * 100 + Date.Month(_)
                                )
                            )
                        ) - 1
                in
                    {1..varMonths}

        ),

 

 

That probably looks complex, but it is a simple concept. Starting from the inside of that working out:

1. Get the open date from the Owners file, and create a list of all dates from the start of that month through the end of the last month.
2. Convert those to dates
3. Convert that to a unique month/year field, so 202001, 202002, 202003, etc.
4. Give me a distinct list of those
5. Count them
6. Subtract 1

Now I have a list of 1..n for each owner. I expanded that to get the column above in the final column.

I also created a Months table that just goes from 1 to 100. Think of this as a pseudo date table

This is the model. It is a set of two perfect Star Schemas. Owners and Months are DIM (Dimension) tables, and Owner Months and Orders are FACT tables.

The result is the matrix shown above. Months from the months table in the columns, owner names in the rows, and the measure is what is above.

 

My file is here. I combined your 3 spreadsheets and am connecting to this single Excel file here. The only thing you might want to do is filter the table so it only shows the top 12 values, not 14 as Brice shows. You probalby don't want the total row either, so just remove that in the Matrix settings.

 

 

I think this is what you want:

 

        let
            varToday = DateTime.Date(DateTime.LocalNow())
        in
        (Date.Year(varToday) - Date.Year([OpenDate])) * 12 +
            (Date.Month(varToday) - Date.Month(OpenDate)) + 1

 

 

You can do it without the variable, but then you have to repeat DateTime.Date(DateTime.LocalNow()) several times. that is the equivalent of TODAY()

I might not have my parenthesis in the same places you needed, so check the math.