Track Customer Sales Through Time


I have tried to solve this issue to no avail so I wanted to reach out to the community for some guidance.

I have three sales categories and when a category is selected along with a period, I want to see only customers who came on in that period for the selected category. I am able to get this view just fine BUT I can’t seem to figure out how to track sales in subsequent months.

For example: if I want to track sales to new customers who came on in Q1 2020, and I select period Q1 2020, my visual is limited to only Q1 2020, how would I see subsequent periods after Q1 2020 for the selected new customers.

I’ve attached a mock up of the data with requirements.

Thank you for any assistance.

MarkSample Data.csv (76.1 KB)

Hi @msedlak.

This is a poster case for data modelling. To start, I imported your base data as a staging query, then created Reference tables for Sales, Customers, and Categories, then added the eDNA extended date table. I then added a calculated column to the Customers table for [Earliest Sale Date] and used this to link to the Dates table.

Earliest Sale Date = 
CALCULATE( MIN( Sales[Order Date]), 
    ALLEXCEPT( Customers, Customers[Customer & State] ) 

I was able to show something, but needed to use a bi-directional relationship (not recommended).

I’m still a little unclear on exactly what you were looking for, but hope this helps

(An Excel mock-up of your expected outcome would allow us to go further if needed.)

eDNA Forum - Track Customer Sales Through Time.pbix (79.8 KB)

1 Like

Hi @msedlak , did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Thank you Greg! I do agree that the bi-directional relationship is not ideal and may present an issue as I continue building out the model.

I apologize for leaving out the mock up. I’m going to spend some time with this today to see how far I can get.

I will report back if I am not able to figure out how to piece this together.

I do appreciate your help with this.


@Greg Ok, I have mocked this up to show where I am hitting a wall. I have built two tables.

  1. Table 1 Showing all customers labeled as new, which means they have been with the company for <= to 24 months.
  2. Table 2 showing new customers based on the month they came on board. You will notice in this table, Sales are only accounted for in the month they started with the company. No futures months sales are accounted for in this table. Note: Customer 12-IN shows 1 sale in Qtr 2, however in Table 1 you can see all the sales for that customer.

I would like to Table 2 to show total sales for all periods where customer has label “New Payor”. It currently only shows sales for the month where payor is considered new. Does this make sense?

New Customer Example.pbix (217.1 KB) Sample Data.xlsx (50.5 KB)

Hi @msedlak. The issue with Customer 12-IN is the data: there is a sale before the record designated as “New Payor” for this customer. If I change the calculation for “New Payor” from

New Payor = IF( Sales[Customer Age] = 1, "New Payor", BLANK() )


New Payor = IF( Sales[Customer Age] <= 1, "New Payor", BLANK() )

I see the “2” you’re looking for in your second matrix.

Or perhaps its just a sample data issue for this customer?

Does this help?


If we track customer 12-IN, they became a new customer in Qtr 2. Total sales in Q2 =2, Q3 = 1, Q4 =6.

My issue is how would I build a table where I can select Qtr 2 period and it shows me 2 things:

  1. Which customers came on in that period
  2. What are total sales for Q2, Q3, Q4

The table on the bottom only shows sales from the month they came onboard but not for future months.

Does that make sense?

Hi @msedlak.

OK, so the culprit was trying to use the same measure in both tables. If you create a new measure to use in the second matrix and remove the filter on the “New Payor” slicer, then you get 2, 1, 6 in the second matrix for Customer 12-IN.

All Sales Since New = 
VAR _NewPayorQuarterStartDate = STARTOFQUARTER( Sales[Payor & State Start Date] )

CALCULATE( [Total Sales],
    ALL( Sales[New Payor] ),
    FILTER( 'Calendar', 'Calendar'[Date] >= _NewPayorQuarterStartDate )

Does this help?

eDNA Forum - Track Customer Sales Through Time V2.pbix (179.6 KB)

@Greg This is great! Thank you and I apologize for my explanation on this one.

@msedlak nothing to apologize for … getting all involved in a question “on the same page” always requires iteration, so expected (can you tell I’m a consultant?). Greg