Forum Discussion

MacedoLuiss's avatar
MacedoLuiss
New Member
6 months ago
Solved

Need help with Dax

Hi, 
I have the a visual (waterfall) that displays the last 12 week. The 12 weeks is filtered using a visual level filter. Now the users need to filter a year, for example: when the year is current year (users might place a filter on year) the visual must remain as it is - displaying last 12w (202548...202606), but this is not happening.

The  way I thought I'd solve the problem was using variable called "tablefilter" and, when I transfered it  to the calculation it didnt work. 
How could I achieve that using Dax?
Like, if user select year, and the year is the latest year, then the waterfall must keeping display the last 12 week. Today, the visual is filtered. The year filter comes from the lateral filter pane!

Code:

VAR tablefilter =
CALCULATETABLE(
        FILTER(
            KEEPFILTERS(
                SUMMARIZECOLUMNS(
                    'dim_calendario'[num_calnweekyear_sellout],
                    'dim_calendario'[num_ano_semana],
                    --"Validação L12W", '_Measure'[Validação L12W],
                    "Market share dinâmico Nestlé 12W", '_Measure'[Market share dinâmico Nestlé 12W]
                )
            ),
            NOT(ISBLANK([Market share dinâmico Nestlé 12W]))
        ),
    ALL(dim_calendario[num_calnyear])
)

VAR ANO_REGRA       = VALUE([Ano_PY] & 52)
VAR WEEK_ATUAL      = MAX(dim_calendario[num_ano_semana])
VAR WEEK_ATERIOR    = MAX(dim_calendario[num_ano_semana]) -1
VAR CALC_WEEK       = VALUE(if(RIGHT(WEEK_ATERIOR,2) = "00", ANO_REGRA,WEEK_ATERIOR))

RETURN
CALCULATE(
    CALCULATE(
        [Market share dinâmico Nestlé 12W],
        FILTER(dim_calendario,dim_calendario[num_ano_semana] = WEEK_ATUAL)
    ) -
    CALCULATE(
        [Market share dinâmico Nestlé 12W],
        FILTER(ALL(dim_calendario),dim_calendario[num_ano_semana] = CALC_WEEK)

    ), 

tablefilter
)

 



Year is filtered:

 




  • Hi,

    Please share the download link of the PBI file.  Ensure that there is a calendar table there with a column of week numbers.

3 Replies