Forum Discussion

Yamabushi's avatar
Yamabushi
Helper I
2 years ago

Help understanding this DAX

Hi,

 

I wish to understand this measure, especially the part in AND():

30 Day Total Sales =
CALCULATE(
    [Total Sales],
    AND(
        DimDate[FullDateAlternateKey] > MAX(DimDate[FullDateAlternateKey]) - 2,
        DimDate[FullDateAlternateKey] <= MAX(DimDate[FullDateAlternateKey])
    )
)
 

 

 
I know that there is an implicit FILTER(ALL()) going on that removes the filter from FullDateAlternateKey, so how does DAX know to use the date where the filter context is removed on the left side of the condition, and the original filter context on the right side of the condition (in MAX()).
 
Thank you!
DAX

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Yamabushi  Please find the summary

    1. MAX(DimDate[FullDateAlternateKey]): This part finds the maximum (latest) date in the DimDate table.

    2. MAX(DimDate[FullDateAlternateKey]) - 2: Subtracting 2 days from the latest date gives you a date that is 2 days ago.

    3. DimDate[FullDateAlternateKey] > MAX(DimDate[FullDateAlternateKey]) - 2: This condition filters dates that are greater than 2 days ago.

    4. DimDate[FullDateAlternateKey] <= MAX(DimDate[FullDateAlternateKey]): This condition filters dates up to the latest date.

    5. AND(...): The AND function combines the two conditions, ensuring that the date is within the last 2 days.

    6. CALCULATE([Total Sales], ...): Finally, the CALCULATE function applies these date filters to the [Total Sales] measure, giving you the total sales for the last 2 days

    And accept as solutin if it helps

    • Yamabushi's avatar
      Yamabushi
      Helper I
      2 years ago

      Hi,

       

      thank you, @bhanu, but that was not what I was asking. I understand what each function individually does. My question specifically relates to evaluation context:

      • Why is DimDate[FullDateAlternateKey] in MAX evaluated in the original evaluation context?
      • Why is DimDate[FullDateAlternateKey] on the left side of the comparison operator evaluated in the evaluation context coming from CALCULATE?
      • In formulas like this, how do we know when something is evaluated in a modified evaluation context, and when something is being evaluated in the original evaluation context?

      Thanks!