cancel
Showing results for
Did you mean:
Highlighted
Member

## Sales KPIs

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.

Chronis

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Sales KPIs

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 min_customer_order_date =
)

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

2 REPLIES 2
Community Support Team

## Re: Sales KPIs

Hi @chronis69,

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Sales KPIs

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 min_customer_order_date =
)

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,205 guests
Recent signins: