Purchase Price Variance Analysis Expression

[#1]

How to write Set Analysis expression to get last year data value for any given item. This is for purchase price variance report based on dynamic selection of year and month. Details are in the attached excel file. Any help would be appreciated.

[Anonymous]

Try something like this

Sum({<[Acct Year]=Max([Acct Year]), [Acct Month]=Max([Acct Month])>}[Quantity Received])

[#3]

Thanks for the reply Kozins but I cannot just use the above expression. If you see the attached file you will understand the issue. The main issue how to get item data of the month of the previous year if the item was not purchased during the selected month of previous year. For example, item A was purchased in Jan 2015 and the same item was purchased in March 2014, June 2014 then I need to get March 2014 value for Jan 2015 as previous year cost.

[buzzy996]

try this,

SUM({<DateDimensionName = {'>=$(=YearStart(Today())) <= $(=Today())'}>} MeasureName)

[#5]

Thanks Shiva but this will only give me the current year value. Please refer to the attached excel file for more info.

[#6]

Can I ask, on Item A, why you would go and get March 2014 rather than an average for the year if it wasn't purchased in that month?  If it was bought was in May 2015, and March 2014 and June 2014, which would you choose, the earlier period or later period?  Just trying to understand better.  I've recently completed a price variance query, and we did more with averages, but never quite got a good answer for when it wasn't sold the exact period in the previous year and continue to look to improve our query.

[#7]

Hi Rhonda,

     Based on the selected month I always looks for a price in the previous year for the selected month or the later month but not the earlier month. For example, if my selected month is March 2015 then I would look for a price in March 2014 and so on but not Jan 2014 or Feb 2014. Hope this answered your question.

[Anonymous]

Check this out. I did the calculations in the backend, not sure what kind of data volume you are delaing with.

See if it helps.

Thanks,

[#9 · Accepted solution]

Thank you for the response Amar. I had already implemented the same logic in my app before your response but I appreciate for responding to my query.