Calculating Stock of the Last Week of the Month - Set Analysis

[#1]

Hi all I have a pivot table in which I am trying to calculate the ending weeks stock:

This in the pivot table I want this to read as £5 rather than a sum of all weeks.

I have tried using variables but I cannot quite get it right:

Then using this expression in the pivot table:

=sum({$<Dim1= {Dim}, Week={'<=$(vMaxCalWeek)'}>} Stock)

I want to keep the sales as a sum of, but the stock to be a sum of the max week (last week of the month/ ending stock)

Any ideas?

Thanks in advance

[Clever_Anjos]

Try FirstSortedValue:

FirstSortedValue({$<Dim1= {Dim}, Week={'<=$(vMaxCalWeek)'}>} Stock, - Week)

[#3]

Unfortunately, this gives a null value. Thanks though

[Clever_Anjos]

Week is a string or number?

[#5]

Week is an integer (Date format)

[Clever_Anjos]

Would you mind sharing a sample of your qvw?

[#7]

You could try something like below to see weekly stock amount next to week row and final weeks stock in total row:

pick(match(Dimensionality(),1,2),sum({$<Week={'=$(vMaxCalWeek)'}>} Stock),

sum({$<Week={'<=$(vMaxCalWeek)'}>} Stock))

[Clever_Anjos]

It´s hard to solve without having a sample of your data, maybe

FirstSortedValue(

  Aggr(

     Sum({$<Dim1= {Dim}, Week={'<=$(vMaxCalWeek)'}>} Stock),

    Week),

- Week)