Dax works with a number but not with a measure

[gesquivel75]

I have a table that shows the total and accumulated sales amounts per day of any two weeks the user selects in a slicer. For example, the user can choose week 50 from 2023 to compare against week 4 of 2024. This formula calculates the accumulated sales amount in the previous week and shows it per day in the table next to the accumulated amount of the current week, so after that I can calculate the difference and %change.

The problem I have is that the WeekDifference measure doesnt work even though it has the correct value, if I replace the WeekDifference with the real number then it works fine. For example if I am comparing week 2 and week 4, week difference = 2, and the measure has the correct result, if I put 2 in the formula it works but if I replace the 2 with the measure then it doesnt work. Help?

Previous Week Accumulated =

 VAR CurrentDate = MAX('Sales Actuals'[DocDate])

RETURN

CALCULATE(

    [Acummdvd],

    FILTER(

        ALLSELECTED('Sales Actuals'),

        'Sales Actuals'[DocDate] <= (CurrentDate - ([WeekDifference]*7)

)

))

[v-kongfanf-msft]

Hi @gesquivel75 ,

Try to modify your formula like below:

Previous Week Accumulated =
VAR CurrentDate =
    MAX ( 'Sales Actuals'[DocDate] )
VAR WeekDifference = [WeekDifference]
RETURN
    CALCULATE (
        [Acummdvd],
        FILTER (
            ALLSELECTED ( 'Sales Actuals' ),
            'Sales Actuals'[DocDate] <= ( CurrentDate - ( WeekDifference * 7 ) )
        )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @gesquivel75 ,

what I forget sometimes is the fact that a measure implicitly is wrapped into a CALCULATE. This means that the evaluation of [WeekDifference] is happening in the row context that is created by the table iterator function FILTER( ... ).

Maybe the measure does not return the expected value 2, when the magic of CALCULATE is implicitly invoked.

Consider to create a variable outside of FILTER like you did with the CurrentDate variable.

You can read about the magic of CALCULATE in this article: https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

Please be warned, this is not an easy article. I still read the article when a measure does not return the expected result , even after many attemps.

Hopefully, this helps to tackle your challenge.

Regards,

Tom