Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Display only the last date in a table pivot

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

Labels (2)

• ### hiding

7 Replies
MVP
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)
Contributor III
Author

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.

MVP
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)
Contributor III
Author

**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.

MVP
I think I got the parenthesis wrong (I where posting from by cell phone).

SUM({&lt; Date = {"\$(= max(Date))"}&gt;} [Spending Total])/(Sum({<DATE>}[Spending Total])) - 1) * Avg(1)</DATE>
MVP
Something went wrong with the formatting.
SUM({< Date = {"\$(= max(Date))"}>} [Spending Total])
/