How to calculate the sales during the first 6 months after accounts created

Hi, Guys, I have a question on DAX and hope to get some helps from experts. In my attached pbix file, I have a simple sales table, customer table, and Date table. On the customer table, each customer has a business start date. The sales for each customer will need to check if the sales date is within 6 months frame of customer start date.And then I need to have a sales chart by month. I don’t know how to write in Dax or if I need to do something in Power Query. Thanks for help
Sales_change_with_Start_Date.pbix (761.0 KB)

@Mike,

Take a look at this and see if it works for you. Just created two measures, the first using DATEADD to step the sales date back by six months, the second filtering total sales by Cust Start Date >= the date created in the first measure.

image

SalesDate Minus 6Mo =

CALCULATE(
    SELECTEDVALUE( Sales[SalesDate] ),
    DATEADD(
        'Date'[Date],
        -6,
        MONTH
    )
)

Total Sales within 6Mo of Cust Start Date = 

CALCULATE(
    [Total Sales],
    FILTER(
        customer,
        customer[BusinessStartDate] >= [SalesDate Minus 6Mo]
    )
)

I hope this is helpful. Full solution file attached.

P.S. Your date table looks a little sad. If you’re going to be doing any significant time intelligence work with this data, I definitely recommend you give it a massive upgrade and switch over to @Melissa’s Extended Date Table.

2 Likes

Hi, Brian, thank you very much for quick response and I will definitely check the Extended Date table.

I did a quick check on the data to validate the solution. I created two tables, shown below. On the left side, it is the table with customer information, and the right table is the monthly sales in 2020. When I selected Jan, 2020 in the table, I expected that only customers within the first 6 months after being created contributed to the Sales of Jan, 2020. Since Jan, 2020 is selected, the created date for those customers should not earlier than July 1st, 2019. However, there are a lot of customers with created date in 2018.

I apologize for any confusion could be caused by my post. Could you please take a further look?

Thanks!

Mike

@Mike,

Thanks very much for the clarification. I know what needs to be done now to make this work, but it seems the customer table is missing essential data that I’ll need to implement the solution. Here’s the scoop:

While the data model looks like a very simple star schema, what you are trying to do in terms of filtering the date from the customer table in effect would require a relationship between customer start date and sales date, but the relationship between the two tables is based on customer ID, which appears nowhere within the evaluation context of this analysis/calculation. In addition, the relationship between these two dates is many-to-many. There are a number of different ways to handle this, but I think the absolute simplest, and most effective from a performance standpoint is to pull the customer start date field into the sales table via the customer ID, and then create two inactive relationships from the date table to the sales table for the sales date and the customer start date, in effect implementing the events in progress pattern (shout out to @greg - the EIP writeup is terrific):

So far, so good. But the problem is when I try to pull this field over from the customer table, there are many customer IDs that appear in the sales table that do not appear in the customer table, making the complete merge impossible.

If you can please revisit your dataset and generate a complete customer table, we should be able to implement a solution quickly on this.

Please let me know if any of the above is unclear. I hope this is helpful.

  • Brian
2 Likes

Hi, Brian, thank you very much for your time, effort and clear explanation on the solution.

Yes, you are right, some of customerID are missing in the Sales table due to the fact that the customer table is the subset of complete customer data. I have made some changes in the sales table to remove those missing customers. However, you will notice that some of CustomerID in customer tables are missing in the Sales table (either due to they are old customers or they have never made purchase after being created), which is the real scenario in this case.

Please see the attachment for PowerBI file.

Again, thank you very much and look forwards to your solution!

Sales_change_with_Start_Date.pbix (479.9 KB)

Mike

@Mike,

Great – thanks. Exactly what I needed.

Note that it’s 100% fine (and very common) to have unique records in your dimension table that don’t show up in your fact table. The problem is when you have records in your fact table that have no match in the relevant dimension table – that was the problem I ran into last night with your previous data.

Should have a revised solution back to you shortly …

  • Brian

@Mike,

Please check to confirm, but I think I now have this working right to your requirements. I ended up stepping the sales date back six months in Power Query using the following M code:

The revised measure now looks like this:

Total Sales_Cust Start Date w/i 6mos of Sales Date = 

VAR Result =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Cust BusinessStartDate] >= Sales[Sales Date Minus 6Months] &&
        Sales[Cust BusinessStartDate] <=  Sales[SalesDate]
    )
)

RETURN
Result 

And here it is all put together:

Please let me know if this gets you what you need or not. Full solution file attached below.

  • Brian

eDNA Forum - Sales_change_with_Start_Date solution2.pbix (577.8 KB)

Hi, I guess I replied too fast and too soon. I added sales date and I know why that happened. It is because the account with business start date July 17, 2020 made purchase on Jan 6, 2020 only.

Let me do more testing.

Thanks!

Mike

Hi, Brian, I did more testing and result is fantastic. That is awesome and you save me tons of time. Thank you very much for your help!

Stay safe and stay healthy!

Mike

@Mike,

Great! – glad to hear that worked well for you. A couple things I forgot to mention - in the solution I sent you, I upgraded your date table to the Extended Date Table so you could play around with it. Also, below is a cheat sheet I put together listing the fields and the formats for each one. Finally, if you’re interested in really leveraging the power of the new date table for other time intelligence functions, @Melissa and I put together a multipart video series on that topic.

Video #2 and #6 (offsets) are the most essential ones IMO if you don’t have time to go through the whole series.

  • Brian

Hi, @BrianJ, Thank you very much for finding the solution for me. I noticed that you have replaced the date table. I love the time intelligence in PowerBi and I will definitely check the series of the video. I am pretty sure that I will benefit from the topics discussed in the videos.

I will start to use the solution you provided in my work project and I will reach out you for any other questions if I have.

Thanks again!

Mike

2 Likes