Calculate daily targets based on monthly targets Sales Power bi

Question

I had the following question which I just can't wrap my head around it to do it in a neat way.

I want to create a line graph with three lines. We call it a budget snake.

• Created sales orders (black)
• Invoiced orders (green)
• Daily targets (red)

This per salesperson. The creation of this graph for the created and invoiced orders is easy as these are all on a daily granularity so creating the line graph is easy.

I just struggle how to create/generate such a line for the targets. In this case, I manually created a table with date - salesperson - daily target Eg.

Which is very cumbersome. What I would like to be able to do is create a table on a monthly level for each salesperson and that PowerBI can "generate/calculate" the daily target in such a way that I can graph the red line without all the hassle of creating it for each salesperson manually.

The input would be something like this

+-----------+----------+--------------+--------+----------------+--------------+---------------+
|   Date    |  Month   | Salesperson  | Branch | Monthly Target | Daily Target | Business days |
+-----------+----------+--------------+--------+----------------+--------------+---------------+
| 1/01/2017 | January  | salesperson1 | test   |          73529 |         4325 |            17 |
| 1/02/2017 | February | salesperson1 | test   |          73529 |         4325 |            20 |
+-----------+----------+--------------+--------+----------------+--------------+---------------+

I have a date dimension table so on my graph I have the date as the x-axis and then the runningorders/runningsales as the y-axis but I would something like a daily runningtarget so that the red line is nicely going with the orders and sales.

I had a look at this pattern but I just cannot figure out how this can generate a line graph.

https://www.daxpatterns.com/budget-patterns/

So somehow, I guess I would need something which generates this first table with the second table as input. I tried some measures in Dax but none of them give me the cumulative steps for each day. It mostly just shows me the value.

These are the measures I use for the other lines. This works nicely when changing the date filters. Running sales

RunningTotalSales = CALCULATE(sum(vw_invoice_trn_summary[NetInvoiceValue]),
                     FILTER(ALLSELECTED(DimTime),DimTime[Date] <= MAX(DimTime[Date])))

Running orders

RunningTotalOrders = CALCULATE(sum(vw_orders_raised[OrderTotal]),FILTER(ALLSELECTED(DimTime),DimTime[Date] <= MAX(DimTime[Date])))

In my current manual solution, the full year though does not work well with the targets line as I am not sure I do it right.

UPDATE

So thinking further about this. It feels like I just need to be able to create a table with a date - daily target - salesperson. based on the monthly targets but not sure how you can do that in power bi. Ideally, you can just add/remove a salesperson and that specific table gets regenerated.

Answer 1

I have two solutions to this. One using DAX and one using the query editor.

---

DAX Solution:

1. Create a calendar table that has all the dates you need.

If Targets is the table containing your monthly targets, create a new table using a formula like this:

Calendar = CALENDAR(EOMONTH(MIN(Targets[Date]),-1)+1,EOMONTH(MAX(Targets[Date]),0))

2. Create a new table DailyTargets as a cross join of your dates and salespersons.

The CROSSJOIN function creates a row for each date and salesperson combination:

DailyTargets = CROSSJOIN(VALUES('Calendar'[Date]),VALUES(Targets[Salesperson]))

3. Create a calculated column for your daily targets.

I do this by looking up the monthly target and dividing by the number of days in the month:

DailyTarget = DIVIDE(
    LOOKUPVALUE(Targets[MonthlyTarget],
                Targets[Month], FORMAT(DailyTargets[Date],"mmmm"),
                Targets[Salesperson], DailyTargets[Salesperson]),
    DAY(EOMONTH(DailyTargets[Date],0)))

Now you have a daily target for each date and each salesperson.

---

PowerQuery Solution:

1. Create a calendar table that has all the dates you need.

Create a blank query and use the following code:

= List.Dates(List.Min(Targets[Date]),
             Duration.Days(Date.EndOfMonth(List.Max(Targets[Date]))
             - List.Min(Targets[Date])) + 1,
             #duration(1,0,0,0))

2. Convert this list to a table.

Click on "To Table" under the Transform tab and rename the column from "Column1" to "Date".

3. Create a custom column for the month name.

You can use the formulaDate.MonthName([Date]) for this.

4. Merge this query with the Targets table (joining on the Month columns).

5. After merging, expand the Salesperson and MonthlyTarget columns.

6. Create the daily target by dividing the monthly target by the number of days in the month.

You can use the formula [MonthlyTarget]/Date.DaysInMonth([Date]) for this.

The entire query should look like this:

let
    Source = List.Dates(List.Min(Targets[Date]), Duration.Days(Date.EndOfMonth(List.Max(Targets[Date])) - List.Min(Targets[Date])) + 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month", each Date.MonthName([Date])),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Month"},Targets,{"Month"},"Targets",JoinKind.LeftOuter),
    #"Expanded Targets" = Table.ExpandTableColumn(#"Merged Queries", "Targets", {"Salesperson", "MonthlyTarget"}, {"Salesperson", "MonthlyTarget"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Targets", "DailyTarget", each [MonthlyTarget]/Date.DaysInMonth([Date]))
in
    #"Added Custom1"

Instead of going step by step, you can just paste this into the Advanced Editor if you'd like. (Just be sure you use whatever table name you have instead of Targets.)