Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX unexpected result when using a Measure inside the FILTER() formula

I have a DAX formula which is working OK:

MAT Sales = 
CALCULATE(
[Total Sales Amount] ;
FILTER(
ALL( 'common Calendar' ) ;
'common Calendar'[DateValue] <= MAX('common Calendar'[DateValue])
&&
'common Calendar'[DateValue]
> MAX('common Calendar'[DateValue]) - 365
)
)

I tried to be smart and replace this part: MAX('common Calendar'[DateValue])
with a measure:
Max Date = MAX('common Calendar'[DateValue])

but somehow that breaks the DAX formula.

In other words, this doesn't work:

MAT Sales = 
CALCULATE(
[Total Sales Amount] ;
FILTER(
ALL( 'common Calendar' ) ;
'common Calendar'[DateValue] <= [Max Date]
&&
'common Calendar'[DateValue]
> [Max Date] - 365
)


Do you have any idea why?

Need Help

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi thijsleufkens,

    I think this is caused context. In first expression the max() will refer to current row's value(it calculate cumulative value of one year), but in second expression(it calculate sum of year of max date and apply this to all records in filter) , it will refer to the max value of current filter. You could refer to my sample to understand.

    Best Regards,
    Zoe Zhi

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi Zoe,

       

      Thanks, I thought this would be the case indeed. However, there is 1 thing I still don't understand.

      When selecting multiple years, the output of the formula is the total of all years combined. Even though the formula has this filter:

      Date > [Max Date] - 365
       
      See Screenshot (I don't seem to be able to post a PBIX in here?):