Need to compare starts for customers for their first 12 months

DAX DAX Calculations
1

I originally posted in another topic but it was closed so was encouraged to start a new one (for those that answered the other post).

We are a franchise company. We need to compare the number inspections (orders) an new location books for their first 24 months they are open against previously opened locations. I need to work with both month and year from opening date. I have the opening date field [Contacts.Open-Date]. Each of our franchise locations start on different months and different years. How do determine the 1st month open, 2nd month, etc to the 24th month.

Once i calculate that, i need to then add up the Inspections performed. I already have a Measure that calculates the inspection counts called [Inspections].

The end result would look like this:

image
image725×174 4.83 KB

2

Hi @ChrisZimmerman

I gave this Solution does it not suits your requirement.

I am handling this by using Calculated Columns using sample data from SAM samples.

  1. Create a Calculated Column to determine first sales for each Category(Customer in my case)MinOrderDate =
    CALCULATE (
    MIN ( Sales[OrderDate] ),
    ALLEXCEPT ( Sales, Sales[Customer Name Index] )
    )
  2. Determine Month number for all other Sales based on the first Sale of Customer.MonthOrder =
    ( YEAR ( Sales[OrderDate] ) - YEAR ( Sales[MinOrderDate] ) ) * 12
  • ( MONTH ( Sales[OrderDate] ) - MONTH ( Sales[MinOrderDate] ) ) + 1
  1. Create a Display Column and Sort By MonthOrder created above.

MonthDisplay = "Month " & Sales[MonthOrder]

Now you can create any Measure like below for showing the desired value

CountSales = COUNTROWS(Sales)

Put all of them in a Matrix and you should get the desired result.

image
image875×255 10.2 KB

EDNA_Month_Matrix_Solution.pbix (1002.6 KB)

Thanks
Ankit Jain

3

Enterprise DNA told me to repost since I had put this on a topic that was closed. So I did. then i saw your response after I posted. I am going to try this today to see if this works. Thanks for the input.

4

No Issues. Check and let me know if any other tweaks needed.

Thanks
Ankit J

5

Point of clarity, in your solution above, the calculated column is looking for the date of the first sale. In my case, I am looking at the opening date as the beginning, regardless if they had any sales (in our case, inspections). We have a field called Open_Date. That will be Month 1. Does that affect the solution?

Here is what i need to accomplish. Instead of calculating from the Sales[MinOrderDate], I need to use the YEAR(Contacts[Open_Date]) from a different table than the Sales table. How would i write the formula above with this in mind:

MonthOrder = (YEAR(Sales[OrderDate]) - YEAR(Contacts[Open_Date]))*12 
(MONTH(Sales[OrderDate]) - MONTH(Contacts[Open_Date]))+1

but i don’t know how to join the Contacts table to the Sales table in the formula (They are joined by a field called CenterID).

6

Hi @ChrisZimmerman

If they have relationship then you can use Related() function to get the Open_Date.

For complete solution, provide a sample Pbix file.

Thanks
Ankit J

7

Hi @ChrisZimmerman,

Since the OpenDate is static, I’d like to offer an alternative approach using Power Query, this will simplify the DAX required.

So I’ve added a column to generate the Bucket labels. This is the logic for that"
let MonthDiff = ((12 * Date.Year(Date.From([InspectionDate]))) + Date.Month(Date.From([InspectionDate])) +1) - ((12 * Date.Year([OpenDate])) + Date.Month([OpenDate])) in if MonthDiff >12 then null else "Month " & Text.From( MonthDiff )

image
image1486×398 23.2 KB

Just added a simple measure to count the rows

Count = COUNTROWS( Contacts )

and here’s the result.
image

I hope this is helpful. Sample file below.
eDNA - Compare customers for their first 12 months.pbix (154.2 KB)

1 Like
8

Hi @ChrisZimmerman, we’ve noticed that no response has been received from you since the 23rd of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

9

Ankit, your solution worked. The Related () function did the trick, i was able to pull in the OpenDate. Thank you for the help. Melissa, i was able to get the other solution to work and was ready to try yours if it didn’t (though i confess i’ve never used power query). thanks for the solutions.

1 Like