Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

DAX Week Filter Function

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
)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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

    IDVENDEDORPERIODCAPITALOPERATIONS
    701/10/2023527074772
    3301/10/2023396398840
    3701/10/2023339470335
    901/10/202312137404160
    501/10/202318302477190
    601/10/20233596126110
    1001/10/2023932938485
    8201/10/2023508496065
    2001/10/2023900708990
    1101/10/20238615403100

    Tabla_Ejecutivos

    IDVENDEDORSELLER
    5EJECUTIVO_1
    6EJECUTIVO_2
    7EJECUTIVO_3
    9EJECUTIVO_4
    10EJECUTIVO_5
    11EJECUTIVO_6
    20EJECUTIVO_7
    33EJECUTIVO_9
    37EJECUTIVO_10
    82EJECUTIVO_11

    Tabla_Ventas

    fechaalta CapitalPedido CodEjecutivo
    09/10/2023 $ 3.180,00

    6

    09/10/2023 $ 21.200,016
    09/10/2023 $ 274.911,825
    07/10/2023 $ 105.839,9982
    07/10/2023 $ 75.599,995
    06/10/2023 $ 5.400,005
    06/10/2023 $ 118.799,999
    06/10/2023 $ 171.570,007
    05/10/2023 $ 97.199,9937
    05/10/2023 $ 75.599,9933
    05/10/2023 $ 41.040,0010
    04/10/2023 $ 21.200,016
    04/10/2023 $ 24.168,0111
    03/10/2023 $ 323.999,9720
    03/10/2023 $ 47.520,0010
    02/10/2023 $ 64.799,9982
    02/10/2023 $ 53.000,015
    02/10/2023 $ 107.999,9933