Calculate Initial Order and Reorders by Customer by Model but allow to filter on date

[Pmike]

Hi,

I am looking for a way to calculate Initial orders and Reorders by Customer by Model.

My data set looks like:

Customer     Model     Date            Quantity

123               A             01/01/17      1

123               A             02/01/17      1

123               A             03/01/17      1

456               A             01/01/17      1

456               A             02/01/17      1

456               A             05/01/17      1

456               B             01/01/17      1

456               B             02/01/17      1

Results should be:

Customer  Model  IntialOrder   Reorders

123            A           1                  2

456            A           1                  2

456            B           1                  1

I want to be able to run this over my date filters so if I say filter from 01/01/17 to 03/01/17 my results would be

Customer  Model  IntialOrder   Reorders

123            A           1                  2

456            A           1                  1

456            B           1                  1

Any help would be greatly appreciated.

[Ashish_Mathur]

Hi,

Are the numbers under thr InitialOrder and Reorders column quantities or are they the number of times an order was placed?

Please clarify.

[Pmike]

They are quantities

[Ashish_Mathur]

Hi,

Create a calendar table with the list of dates contained in the Sales data table.  In the slicer/filter, pull dates from the Calendar table and make any selection.  Try these measures:

InitialOrder

=CALCULATE(SUM(Data[Quantity]),MIN(Calendar[Date]))

Reorders

=CALCULATE(SUM(Data[Quantity]),FILTER(Calendar,Calendar[Date]>MIN(Calendar[Date])))

Hope this helps.

[Pmike]

Ashish,

The Intial order calc gives me an error that reads:

A function "MIN" has been used in a True/False expression that is used as a table filter expression.  This is not allowed.

The Reorder calc is including all and not just the Reorders

Do you know how I correct this?

Thanks

[Ashish_Mathur]

Hi @Pmike,

Try these formulas

InitialOrder=CALCULATE(SUM(Data[Quantity]),FILTER(data,Data[Date]=MIN(Data[Date])))

Reorders=CALCULATE(SUM(Data[Quantity]),FILTER(Data,Data[Date]>MIN(Data[Date])))

Hope this helps.

[Pmike]

@Ashish_Mathur, thank you very much.  This appears to work, however I have an issue I did not forsee.  I have some records where the initial order is 0 and I want it to skip the zeros and move to the first one that is not zero.

Any idea.  I have tried to do dual filter, but does not work.

Initial = Calculate(sum(dbo_ORDERS[NETSALESUNITS]),filter(dbo_ORDERS,dbo_ORDERS[NETSALESUNITS]>0),filter(dbo_ORDERS,dbo_ORDERS[Date]=min(dbo_ORDERS[Date])))

Thanks,

Pmike.

[Ashish_Mathur]

Hi,

Try this

=InitialOrder=CALCULATE(SUM(Data[Quantity]),FILTER(data,Data[Date]=MIN(Data[Date]&&Data[Quantity]>0)))

Hope this helps.

[Pmike]

@Ashish_Mathur,

So I modified the formula a bit utilizing the pieces you gave me.  

Qty 1st Purchased = Var firsttimepurchase= CALCULATE(min(Sheet1[Date]), ALLEXCEPT(Sheet1,Sheet1[Sales Rep],Sheet1[Model])) return CALCULATE(sum(Sheet1[Qty Shipped]),FILTER(Sheet1,Sheet1[Date]<=firsttimepurchase&&Sheet1[Qty Shipped]>0))

So my data I load in looks like this:

Sales Rep	Order	Date	Model	Qty Shipped
John	565656	4/1/2017	ABCDE	2
John	565657	4/2/2017	ABCDE	1
Jim	565658	5/1/2017	ABCDE	1
Jim	565659	5/2/2017	ABCDE	2
John	565660	6/1/2017	BCDEF	1
Jim	565661	6/2/2017	BCDEF	2
John	565660	7/1/2017	BCDEF	1
Jim	565661	7/2/2017	BCDEF	2

My Results are:

As you can see, the formula identifies the Quantity First Purchased for Each Customer and Each Model.  So I am very grateful this now appears right.

However - do you notice the total does not sum right. 

Do you know how I can fix that?  

I want to be able to remove Sales Rep and Model and have it show me total of 6

Thanks for your help with this.

PMike

[Ashish_Mathur]

Hi,

I am now confused with your question.  In your original post you showed Model and sales rep in your visual.  Now you say that you do not want it in the visual.  Share the final dataset and more importantly show your result very clearly.

[Pmike]

I am trying to grab the first record as initial.

I tried this:  Initial = CALCULATE(SUM(dbo_ORDERS[netsalesUNITS]),FIRSTDATE(dbo_ORDERS[Date]),dbo_ORDERS[NETSALESDOLLARS]>0)

But it brings all records:

Store   Model   Initial   Date          Order Num

123      ABC      2           4/19/17    56555

123      ABC      1           4/25/17    56556

I only want to see the first record in my matrix, but it keeps bringing down all records.

Do I need to filter this first to only look at first record for every customer and model?