Display only the last date in a table pivot

[lotitolo]

Hello,

I would like to know what the procedure is to display only the last month of my pivot table dynamically.

Indeed when I use the filter function I lose my evolution calculations from the current month to the previous month (and in addition to that, it applies to the other calculation of my sheet, which I don't want)

Here is a small picture to illustrate the situation:

Ths,

Lotitolo

[Vegar]

You could do this by using SET analysis in your expression.
I assume you have an expression like this
SUM( SomeField)/ sum(somethingElse)

Change it to
SUM({< Date = {"$(= max(Date))"}>} SomeField)/ sum({< Date = {"$(= max(Date))"}>} somethingElse)

[lotitolo]

Wow, thank you.

It works perfectly in terms of the amount:

SUM({< Date = {"$(= max(Date))"}>>} [Spending Total])/1000

But, where it gets complicated is when I want to compare the value of the current month to the previous month:

SUM({< Date = {"$(= max(Date))"}>>} [Spending Total])/Before(Sum({<month>}[Spending Total])) - 1) * Avg(1)

The formula is good, but I have no value displayed.

[Vegar]

This could be solved with set as well. Try something like this.

SUM({< Date = {"$(= max(Date))"}>} [Spending Total])/Before(Sum({<Date = {"$(= addmonths(max(Date)),-1)"}>}[Spending Total])) - 1) * Avg(1)

[lotitolo]

**bleep**, it's impossible to operate the function after the slash "/".
UM({< Date = {"$(= max(Date))"}>>} [Spending Total])/Before(Sum({<Date = {"$(= addmonths(max(Date)),-1)"}>}[Spending Total])) - 1) * Avg(1)

I tried the exercise with a little bit  simpler function (have the % on the total of the column):
SUM({< Date = {"$(= max(Date))"}>>} / sum(TOTAL < Date = {"$(= max(Date))"}>>[Spending Total])

Nothing matters... however the code seems logical to me.

[Vegar]

I think I got the parenthesis wrong (I where posting from by cell phone).
Try the adjusted expression below.

SUM({&lt; Date = {"$(= max(Date))"}&gt;} [Spending Total])/(Sum({<DATE>}[Spending Total])) - 1) * Avg(1)</DATE>

[Vegar]

Something went wrong with the formatting.
SUM({< Date = {"$(= max(Date))"}>} [Spending Total])
/
SUM({<Date = {"$(= addmonths(max(Date),-1))"}>}[Spending Total])

[lotitolo]

I found out why it didn't work for the last measure.
Because for the first measure I only want to display data from the last month. And when I do that, I don't have previous month's sales.
So it is impossible to compare month N with month N-1.
It's painful this display management ^^