How to calculate sales goal based on prior year sales

[Shelley]

Team,

I have a sales database with sales by order# by customer by month. I want to use ALL prior year's sales by customer and multiply it by 1.2 to calculate each customer's goal for the following year. In order to then show goal by month or quarter, I need to divide (last year's sales*1.2) by 12. We want the goal spread evenly across the 12 months, no matter what their sales were by month for the previous year. So for example, we want to plot a straight line by month or quarter for the goal across a bar chart, showing total revenue towards goal.

I've tried this: Sales_Goal_Measure = (CALCULATE((SUM([Total_Revenue]))*1.2, PARALLELPERIOD(PLS_Analytics[Posting_date],-1,YEAR)))

But, when I try to look at the goal by quarter or month, it is not spread evenly. It is calculated based purely on the prior year's performance for that timeframe, not the whole year spread evenly by month or quarter. In addition, I must select at least two years' worth of data in the slicer for the chart to plot both datasets (goal and actual sales).

I've also tried this and it really isn't calculating properly - and I can't tell what it's doing, so I must really have something messed up in the formula:

Measure_Sales_Goal = CALCULATE((SUM(Tablename[Total_Revenue]))*1.2/12,FILTER(Tablename,PLS_Analytics[FISCAL_YEAR]=MAX(Tablename[FISCAL_YEAR])-1))

We could load a separate table for goals, but we're wondering if there's a way to do it within Power BI because the data to calculate the goal is already here.

Sample Data

Order#  Customer#   Month   Qtr  Yr Total Revenue

1               10                6           3             $100

2               11                7           3              $250

3               12                1           1              $400

4               13                12         4              $850

All help is appreciated as I am new to Power BI. Thanks!

[v-sihou-msft]

@Shelley

In DAX, the measure is sliced by current row context. You calculate Total_Revenue * 1.2 based on same period last year, this measure will be sliced by the field you drag into visual as well. So if you put month/quarter into visual, the measure will just calculate last year monthly/quarterly total * 1.2. It's not possible to force the Sale_Goal Yearly total evenly spread on each month/quarter.

In your scenario, you have to separate monthly, quarterly goal into multiple measures.

Measure_Sales_Goal_Monthly =
CALCULATE (
    ( SUM ( Tablename[Total_Revenue] ) ),
    FILTER (
        ALL ( Tablename ),
        PLS_Analytics[FISCAL_YEAR]
            = MAX ( Tablename[FISCAL_YEAR] ) - 1
    )
)
    * 1.2
    / 12

Measure_Sales_Goal_Quarterly =
CALCULATE (
    ( SUM ( Tablename[Total_Revenue] ) ),
    FILTER (
        ALL ( Tablename ),
        PLS_Analytics[FISCAL_YEAR]
            = MAX ( Tablename[FISCAL_YEAR] ) - 1
    )
)
    * 1.2
    / 4

Regards,

[Shelley]

@v-sihou-msft Thanks for your help. This is cool, but it seems to be calculating the total goal over all the sales data. I am actually looking for the goal by each customer (for customers that are a specific distributor type = CMP). All data is in one table too, so maybe this is complicating things?

Here's what I did, with your guidance:

Measure_Sales_Goal_Monthly = CALCULATE((SUM(PLS_Analytics[Total_Revenue])),FILTER(ALL(PLS_Analytics), PLS_Analytics[FISCAL_YEAR]=MAX(PLS_Analytics[FISCAL_YEAR])-1))*1.2/12

Measure_Sales_Goal_Quarterly = CALCULATE((SUM(PLS_Analytics[Total_Revenue])), FILTER(ALL(PLS_Analytics),PLS_Analytics[FISCAL_YEAR]=MAX(PLS_Analytics[FISCAL_YEAR])-1))*1.2/4

Thanks!

Shelley