Sales KPIs

[Anonymous]

Hi

I have the following challenge. My sales table looks like the following:

<timestamp>, <reseller>, <customer>, <sales_value>

I want to be able to record the following KPIs for each month

1. Total monthly sales

2. Total monthly sales from new resellers (new is a reseller how has placed his first order the month in question)

3. Total monthly sales from new customers (new is a reseller how has placed his first order the month in question) 

4. pct of new_reseller_sales/total_sales (in this month)

5. pct of new_customer_sales/total_sales (in this month)

I'm sure that this is very easy to do in DAX and it would take me about 15mins to hack it in XLS but im in the early stages of the DAX learning curve.

Thank you in advance

Chronis

[v-frfei-msft]

Hi @Anonymous,

Could you please share your sample data and excepted result to me? You can upload your file to dropbox and share the link here.

Regards,

Frank

[Anonymous · Accepted solution]

@v-frfei-msft hi

I've had a discussion with a colleague over the weekend and he has helped me produce a solution.

Basically, for every order record we have created a new column "new partner sale" and a new column "new customer sale" that would take 1 if the said order is from a partner/customer who has put his first order the same month of the order in question (i.e the min timestamp for the particular partner/customer is the same of the order in question)

this gave me something to count in terms of new orders. doing a SUMX with the sales amount muliplied with the the value of these I created a measure that contains only the new sales of any one month.

QED... works perfectly. 

My new column looks something like this

_new customer order =
-- returns 1 if the current order is at the first month the end-customer EVER placed an order, 0 otherwise
-- we try to count the orders that are repeat sales from an end-customer vs first time orders (in the context of a month)
VAR current_customer = O365LicenseReport[Customer Company Name]
VAR current_order_month = YEAR(O365LicenseReport[OrderCreationDate])+12*MONTH(O365LicenseReport[OrderCreationDate])
VAR min_customer_order_date =
CALCULATE(MIN(O365LicenseReport[OrderCreationDate]);
FILTER (ALL(O365LicenseReport);O365LicenseReport[Customer Company Name] = current_customer && O365LicenseReport[Vendor]="Microsoft")
)

VAR min_order_month = YEAR(min_customer_order_date)+12*MONTH(min_customer_order_date)
VAR new_order = if(current_order_month=min_order_month;1;0)
RETURN new_order

then the measure is the following

Licenses new customer = sumx(O365LicenseReport;O365LicenseReport[Licenses]*O365LicenseReport[_new customer order])