How to get Last year same period Stock Amount ?

[paulyeo11]

Hi All

May i know how to change the below SET expression , to get Last year same period Stock Amount ?

Money(RangeSum(
Above(RangeSum(Above(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), 0, RowNo()))),
-Sum({<GL_CODE = {50010}>}TOTAL Aggr(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), YearMonth)))
, $(vMoneyFormatK_GL))

So that my Table 2 have  one more column display LY same period stock amount.

Paul

[paulyeo11]

My QVF

[sunny_talwar]

What is the number that you are expecting to see?

[paulyeo11]

[sunny_talwar]

Slightly more complicated then I imagined, I will work on this at a later time

[sunny_talwar · Accepted solution]

Try this:

Money(RangeSum(Sum({<year, month, GL_CODE = {50010}>}[Amount]*-1),

Sum({<year, month, GL_CODE = {50010}, YearMonth = {"=YearMonth > AddYears(Max(TOTAL YearMonth), -1) and YearMonth <= Max(TOTAL YearMonth)"}>}[Amount]*1))

, $(vMoneyFormatK_GL))

[paulyeo11]

Hi Sunny

Can you also give me last month Amount ? it should be 1.413 million.

Paul

[sunny_talwar]

Try this:

Money(RangeSum(Sum({<year, month, GL_CODE = {50010}>}[Amount]*-1),

Sum({<year, month, GL_CODE = {50010}, YearMonth = {"=YearMonth > AddMonths(Max(TOTAL YearMonth), -1) and YearMonth <= Max(TOTAL YearMonth)"}>}[Amount]*1))

, $(vMoneyFormatK_GL))

[paulyeo11]

I move this to the top , as i am working on this