We are a franchisor. I need to calculate inspection counts for our new owners for their first 12 months. starting months are unique to the owner. If they did not have any inspections for the month, want to insert a zero. However, if the owner current month is month 6, then don't want a zero in months 7 - 12 but rather a blank. Have an owner table that looks like this:
The order table looks like this:
We want the results to look like this (this can either be a new table or just add 12 columns to the Owner Table... Month 01, Month 02, etc)
Sample data files are attached. My issue is getting a zero in the blank months before the current month, and the months after the current month staying blank (blank because they haven't been open that long). I can provide sample data files.
Chris
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:
- 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.
- Convert those to dates
- Convert that to a unique month/year field, so 202001, 202002, 202003, etc.
- Give me a distinct list of those
- Count them
- 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.
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:
- 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.
- Convert those to dates
- Convert that to a unique month/year field, so 202001, 202002, 202003, etc.
- Give me a distinct list of those
- Count them
- 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.
Your code is working beautifully. One additional question. I am filtering months to just first 12 months. If i have a row that hasn't completed 12 months, it won't calculate the column total. I can't seem to get this to work.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
