Re: DAX Week Filter Function

Syndicate_Admin · ‎11-01-2023

Good day, could you help me with a feature created which takes a monthly sales goal and breaks it down into different percentages depending on the week of the month that the day slicer filter is selected.

The drawback I have is that it doesn't filter the value of the calculation, it only shows me the total goal.
Attached measure created, thank you very much in advance

Total Target =

WHERE ObjetivoMensual = SUM(LOAN_OBJETIVOS_VENDEDOR[CAPITAL])

WHERE ObjetivoSemana1 = ObjetivoMensual * 0.20

WHERE ObjetivoSemana2 = ObjetivoSemana1 + (ObjetivoMensual * 0.35)

WHERE ObjetivoSemana3 = ObjetivoSemana2 + (ObjetivoMensual * 0.25)

WHERE ObjetivoSemana4 = ObjetivoSemana3 + (ObjetivoMensual * 0.20)

WHERE DiaSeleccionado = SELECTEDVALUE(CALENDAR[Day])

RETURN

SWITCH(TRUE(),

ISINSCOPE(LOAN_OBJETIVOS_VENDEDOR),

SWITCH(TRUE(),

VALUES(CALENDAR[Week of the Month]) = 1 , ObjetivoSemana1,

VALUES(CALENDAR[Week of the Month]) = 2 , ObjetivoSemana2,

VALUES(CALENDAR[Week of the Month]) = 3 , ObjetivoSemana3,

VALUES(CALENDAR[Week of the Month]) = 4 , ObjetivoSemana4

),

ObjetivoMensual

)

Syndicate_Admin · ‎11-03-2023

Good day, thank you very much for your collaboration, at the moment it is not working for me, it is not leaking me weekly goal data, for example if the day slicer is on day 9 it still shows me the monthly goal not the weekly one, I attach some data from the tables to see if it gives you more data to help me.

Tabla_Objetivos

IDVENDEDOR	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

Anonymous · ‎11-02-2023

Hi

Can you try this and let me know the results?

RETURN
SWITCH(TRUE(),
ISINSCOPE(LOAN_OBJETIVOS_VENDEDOR),
SWITCH(TRUE(),
SELECTEDVALUE(CALENDAR[Week of the Month]) = 1 , ObjetivoSemana1,
SELECTEDVALUE(CALENDAR[Week of the Month]) = 2 , ObjetivoSemana2,
SELECTEDVALUE(CALENDAR[Week of the Month]) = 3 , ObjetivoSemana3,
SELECTEDVALUE(CALENDAR[Week of the Month]) = 4 , ObjetivoSemana4
),
ObjetivoMensual
)

If this doesn't work, can you post example data and expected output without privacy? That would be helpful.
How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum