cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate orders for months open only

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

2 ACCEPTED SOLUTIONS
Super User

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" =
Source,
"MonthCount",
each
let
varMonths =
List.Count(
List.Distinct(
List.Transform(
List.Transform(
{
Number.From(
Date.StartOfMonth([OpenDate])
)..
Number.From(
Date.EndOfMonth(
)
)
},
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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
8 REPLIES 8
Super User

Data files weren't attached @chrisazimmerman

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Regular Visitor

Super User

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" =
Source,
"MonthCount",
each
let
varMonths =
List.Count(
List.Distinct(
List.Transform(
List.Transform(
{
Number.From(
Date.StartOfMonth([OpenDate])
)..
Number.From(
Date.EndOfMonth(
)
)
},
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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Regular Visitor

Thanks Edhans.  Appreciate all the work.  This has been an outstanding challenge for us.

Super User

Great @chrisazimmerman - glad I was able to help. Sometimes the best Power BI solutions involve a little it of Power Query, a little bit of modeling, and a little bit of DAX. 😁

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Regular Visitor

One more question if you would.  How do i write this DAX formula in Power Query (i'm pretty new to working in Power Query):

CurrentMonth = ((YEAR(TODAY()) - YEAR('Owners'[OpenDate].[Date]))*12)+((MONTH(TODAY()) - MONTH('Owners'[OpenDate].[Date]))+1)

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Regular Visitor

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.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.