Forum Discussion
Weekly Target Calculation Dax
Good day, I need help to create a dax measure, I have a monthly sales goal per executive, what I need to the extent is that to that monthly goal depending on the week of the month in which we are that using the day segmenter, I get 20% of the goal in week 1, week 2 35%, Week 3 25% and week 4 20%, they should be added if you go from one week to the next and in turn I need that week goal to be distributed in the number of days I have in the week.
The column in the calendar table that I have created is composed of the following dax
Thank you very much in advance.
9 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Good evening, I'm going to detail as best I can the problem I have with calculation:
I have a table which keeps the monthly sales target capital amount of the executiveIn turn, that monthly goal, depending on the week of the month, takes a percentage of the monthly goal to have a weekly goal, for example:
Week1 = 20% of objetivo_mensual
Week2 = 35% of objetivo_mensualWeek3 = 25% of the objetivo_mensual
week4 = 20% of the objetivo_mensual
What I now need is that the amount taken out in each week is redistributed in the number of days you have each week, the drawback I have is that when I use the SLICER of days attached it does not filter me by week, that is, it does not vary the weekly amount of the objective.
On the other hand, I need that amount that was calculated weekly to be distributed among the number of days you have each week, since that calculation is compared with the daily sales to be informed of the missing or surplus amount that there is in the sales, I only have problems with the sales objectivesSecond request: Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Good afternoon, I attach tables to specify information:
Tablo_ObjetivosIDVENDEDOR PERIOD CAPITAL OPERATIONS 7 01/10/2023 5270747 72 33 01/10/2023 3963988 40 37 01/10/2023 3394703 35 9 01/10/2023 12137404 160 5 01/10/2023 18302477 190 6 01/10/2023 3596126 110 10 01/10/2023 9329384 85 82 01/10/2023 5084960 65 20 01/10/2023 9007089 90 11 01/10/2023 8615403 100 Tabla_Ejecutivos
IDVENDEDOR SELLER 5 EJECUTIVO_1 6 EJECUTIVO_2 7 EJECUTIVO_3 9 EJECUTIVO_4 10 EJECUTIVO_5 11 EJECUTIVO_6 20 EJECUTIVO_7 33 EJECUTIVO_9 37 EJECUTIVO_10 82 EJECUTIVO_11 Tabla_Ventas
fechaalta CapitalPedido CodEjecutivo 09/10/2023 $ 3.180,00 6 09/10/2023 $ 21.200,01 6 09/10/2023 $ 274.911,82 5 07/10/2023 $ 105.839,99 82 07/10/2023 $ 75.599,99 5 06/10/2023 $ 5.400,00 5 06/10/2023 $ 118.799,99 9 06/10/2023 $ 171.570,00 7 05/10/2023 $ 97.199,99 37 05/10/2023 $ 75.599,99 33 05/10/2023 $ 41.040,00 10 04/10/2023 $ 21.200,01 6 04/10/2023 $ 24.168,01 11 03/10/2023 $ 323.999,97 20 03/10/2023 $ 47.520,00 10 02/10/2023 $ 64.799,99 82 02/10/2023 $ 53.000,01 5 02/10/2023 $ 107.999,99 33 Please indicate what you mean by "week". Ideally your data model should have a Calendar table that clearly indicates week numbers, especially if you are attempting to project weeks onto months.
Good evening, the model does have a calendar table, but for the calculation of the week as the company takes it is the one I attached above, I don't know if it is possible to attach a pbix file with a model summarized in only the tables that intervene in the calculations, I was trying with some formulas to be able to calculate the percentage and use it in the table but I can't get it to filter inside the table I don't know If you could help me with that calculation, the measurement I'm using is as follows
Weekly Goal =
VAR MonthlyTarget = SUM(tabla_objetivo[MonthlyTarget])
VAR TargetWeek1 = MonthlyTarget * 0.20
VAR TargetWeek2 = TargetWeek1 + (MonthlyTarget * 0.35)
VAR TargetWeek3 = TargetWeek2 + (MonthlyTarget * 0.25)
VAR TargetWeek4 = TargetWeek3 + (MonthlyTarget * 0.20)
RETURN
SWITCH(TRUE(),
ISINSCOPE(tabla_objetivo[Vendor]),
SWITCH(TRUE(),
VALUES('Calendar'[Week]) = 1,TargetWeek1,
VALUES('Calendar'[Week]) = 2, WeekGoal2,
VALUES('Calendar'[Week]) = 3, WeekGoal3,
VALUES('Calendar'[Week]) = 4, WeekGoal4
),
MonthlyGoal
)
But moving the slicer from days to days sets the monthly targetI don't know if it is possible to attach a pbix filePlease follow the instructions in the link I posted (twice).
Sorry, the image is just an example of my table, I attached the necessary data and formulas that I am using to request your collaboration, I don't know what else you need me to attach to you, calendar table cannot be attached to you due to the number of columns it has
I hope someone else will be able to assist you.
