cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisazimmerman
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:  

 

chrisazimmerman_0-1621875552490.png

 

The order table looks like this:  

chrisazimmerman_1-1621875583201.png

 

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)

 

chrisazimmerman_2-1621875644045.png

 

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

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

 

 

edhans_0-1621886712407.png

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:

edhans_1-1621886830507.png

 

 

    #"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.

edhans_2-1621887111765.png

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 I answer your question? Mark my post as a solution!
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

View solution in original post

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 I answer your question? Mark my post as a solution!
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

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

Data files weren't attached @chrisazimmerman 

 

How to get good help fast. Help us help you.

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 I answer your question? Mark my post as a solution!
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

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

 

 

edhans_0-1621886712407.png

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:

edhans_1-1621886830507.png

 

 

    #"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.

edhans_2-1621887111765.png

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 I answer your question? Mark my post as a solution!
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

View solution in original post

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

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 I answer your question? Mark my post as a solution!
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

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)

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 I answer your question? Mark my post as a solution!
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

View solution in original post

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.  

chrisazimmerman_0-1622652496080.png

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors