Calculate daily targets based on monthly targets

[kvbe]

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 sales person.

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 sales person 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 createing 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 changeing 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.

 

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 sales person and that specific table gets regenerated.

[Harpreet1405_12]

Hello,

I need your help to set the targets for the upcoming months (Oct 2022- SEPT 2023) BASED on the historical data,  I have the historical data from 2019 to 2022, so we go like Oct 2020-Sept 2021, Oct 2021-Sept 2022, Oct 2022-Sept 2023(I want to set the targets for these months) for 80 different programs or categories.

Is There any formula or any Dax function available.

I will highly appreciate the response.

Thank you

[v-jiascu-msft]

Hi @kvbe,

Can you share a dummy sample file please? According to your description, it should be easy to make graphs. So I think there is something wrong in your data model. 

Best Regards,

Dale

[kvbe · Accepted solution]

Hi All,

So I solved the solution based on some help from someone on stackoverflow. (I was not sure which forum was the best to use so a bit bad from me to double post it) 

So in the end the proposed solution was to:

• Use Cross Join to generate the list of salesperson per date (which was the basic trick I was not aware of)
• Create a lookup to lookup / calculate the daily target per sales person

After connecting up my datetable correctly the calculations are now nicely done based on the selected dates. 

I made the mistake initially to use stepped targets, which I should just have put all as the same value for each day and just cumulative add them up so I do not get the stepped graph on a year level.

You can find the full answer here. 

https://stackoverflow.com/questions/48468658/calculate-daily-targets-based-on-monthly-targets-sales-...