Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gesquivel75
Regular Visitor

Dax works with a number but not with a measure

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)
)
))
2 REPLIES 2
v-kongfanf-msft
Community Support
Community Support

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.