Sum for DateRange

[Luca1]

I want to sum up all Sales that are further in the past than 365 days. However, my current code returns 0. I suspect that there are some formatting issues with the max() function and the YearMonth format, but I'm not sure about that. Thank you for your help!

=sum(
      {
            <[OrderDate.autoCalendar.YearMonth]
            = {'< max([OrderDate.autoCalendar.YearMonth]) - 365'}
            >
      }
Sales
)

[Digvijay_Singh]

Try this may be, assuming YearMonth is of type date.

=sum(
      {
            <[OrderDate.autoCalendar.YearMonth]
            = {<$(=max([OrderDate.autoCalendar.YearMonth]) - 365)}
            >
      }
Sales
)

[Luca1]

Hey, unfortunately this didn't help - I'm still getting 0 as a result even though that shouldn't be the case. But I appreciate your support!

[Digvijay_Singh]

Can you see at the bottom of the expression editor, normally I correct my formatting by looking at what I see over there...

[Vegar]

Could it be that you are making selections that are in conflict with your set expression?

What happens if you only make selections in the [OrderDate.autoCalendar.YearMonth] field?

You could also try to put double quotes around your modifier like I've done in my expression below.

=sum({< [OrderDate.autoCalendar.YearMonth] =  {"< $(=addyears(max([OrderDate.autoCalendar.YearMonth]),-1))"}>}Sales)

[Luca1]

Your suggestion doesn't work neither. Let me maybe elaborate on stuff that I've tried and behavior that I could observe:
1. If I use 

=sum({<[OrderDate.autoCalendar.Year]={"2017"}>} Sales)

 the output will be reasonable. 

2. If try to achieve the same using

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

the output is 0.

3. However, if I put 

=max([OrderDate.autoCalendar.Year])

to a separate KPI cell, the output is 2017 (as expected). 

Therefore, I think that you are right about that the set expression causes the error. I just don't understand what I do wrong. 

[Vegar · Accepted solution]

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

You need to dollarexpand your expression in order to get the expression above to to work. Try changing it as I did below.

sum({<[OrderDate.autoCalendar.Year]={"$(=max(OrderDate.autoCalendar.Year))"}>)

[Luca1]

Thank you so much, this was my mistake!