Sales dynamics

[PapaJapa]

Hi, I have a problem with preparing sales dynamics month to month. With weeks and years it worked:

=Sum({$<YearField={$(=max(YearField))}>}[Sales])-Sum({$<YearField={$(=max(YearField)-1)}>}[Sales])
=Sum({$<WeekField={$(=max(WeekField))}>}[Sales])-Sum({$<WeekField={$(=max(WeekField)-1)}>}[Sales])

but months in this expression don't work:
=Sum({$<MonthField={$(=max(MonthField))}>}[Sales])-Sum({$<MonthField={$(=max(MonthField)-1)}>}[Sales])

Please help

M

[Vegar]

Is your month field a dual? I other words, it got a presentation value and a numeric value? Maybe you created it by using the month() function ?

If the answer is yes, then please be aware when working with comparing dual value fields in a set modifier it will always do a text comparison. Max(MonthField) will return a numeric value, hence {$<MonthField={$(=max(MonthField))}>} will neveer return any value. Try changing the max() to a maxstring() and you will probably solve your issue.

=Sum({$<MonthField={$(=maxstring(MonthField))}>}[Sales])-Sum({$<MonthField={$(=maxstring(MonthField)-1)}>}[Sales])

[PapaJapa]

Thanks Vegar, but unfortunately it didn't help. The chart only shows the max (MonthField) value of the current selection. The second part of the expression does not work, where sales from the previous month should be deducted.

[Vegar · Accepted solution]

You are right. It is is a bit tricky reducing month by one. For February to December it is not any big issues, but as  January (1) - 1 = 0 you can get in trouble. Therefore I'm replacing your -1 with an addmonths(...,-1). 

Try this expression:

=Sum({$<MonthField={$(=maxstring(MonthField))}>}[Sales])
-
Sum({$<MonthField={"$(=month(addmonths(date#(max(MonthField),'M'),-1)))"}>}[Sales])

[PapaJapa]

It works 🙂 I only have a problem when I choose January for analysis, but this is cosmetics. Thank you very much. Greetings.