Current Year vs Last year

[#1]

Hi guys,

I have a table with dates like : Jan-2015

I need to create a column in pivot table, where I'll calculate the difference between the Month of Current year vs Month of Last year.

Any ideas?

[morganaaron]

Hi Diana,

Do your dates come from a generated date table, i.e. is this 'Month-Year' attached to the Month and Year of dates in your data model?

If so, you can use set analysis on the year - Sum({<Year={2015}>}Sales) - Sum({<Year={2014}>}Sales) for example. The years can be calculated here, so you could use ={'$(=Max(Year))'} and {'$(=Max(Year)-1)'} as a comparison.

If not, you can either choose to create these fields in your script from your date field (i.e. Right(Date, 4) as Year would give you the year for each date, resident load this against the table where your date field comes from or create a master calendar - look this up if need be!) or you can try to calculate this inside of your expression.

[buzzy996]

try this,

=(num(sum({$<Year = {$(=only(Year))}, Month = {"<=$(=max(Month))"}>} Amount), '$#,##0;($#,##0)'))-

(num(sum({$<Year = {$(=only(Year) - 1)}, Month = {"<=$(=max(Month))"}>} Amount), '$#,##0;($#,##0)'))

here,amount is ur measure value and Year ie current year and month is current month u can pass.