Search code examples
powerbidaxpowerbi-desktop

PowerBI: Assign Monthly Sales Target to individual Salesperson on a daily level

I have a concern/inquiry about how to apply monthly level "sales target" on a daily, or weekly level. Right now I have a separate "SalesTarget" table which does not really have a primary key because they are redundant, due to having different sales target for different month.

For example:

1. "salesperson": 10, "Start of Month": 2023-01-01, "Monthly Target": $30000, "Daily Target": $1500   
2. "salesperson": 20, "Start of Month": 2023-01-01, "Monthly Target": $50000, "Daily Target": $2000 
3. "salesperson": 10, "Start of Month": 2023-02-01, "Monthly Target": $40000, "Daily Target": $1600   
4. "salesperson": 20, "Start of Month": 2023-02-01, "Monthly Target": $60000, "Daily Target": $2500 
...

(Which means both keys "salesperson" and "Month", are not unique)

My relationship model looks like the following:

Data Model

In this model, -salesperson connects to salesbudget on salesperson_id (1-many) -calendar connects to salesbudget (1-many) on left = 'full_date', right = 'start of month'

Visual:

  1. When i select on a monthly level or above, everything works fine:

img2

Problem: 2. When I select on a daily or weekly level, its not showing up the targets since there is only relationship on the "start of month" but not only other dates.

img3

Potential solution:

  1. In the SalesBudget Table, duplicate the salesbudgets for all the days and use "first" when calculating DAX for sales budget (currently using SUM(SalesBudget)['Monthly Budget'] Potential issue: SalesBudget table will not be very clean as for each saleseprson_id, there needs to be 365 rows for a year, and i have around 20 salesperson, 6 years data. Which I dont think is the best solution.

Question: Is there a better way to achieve this goal? Thank you!

Solution

  • You can try this for always starting at the start of the month.

    Budget = 
  CALCULATE(
    SUM(SalesBudget[Monthly_Budget]),
    DATESBETWEEN(
      calender[full_date],
      STARTOFMONTH(calender[full_date]),
      MAX(calender[full_date])
    )
  )