Current and Previous Year/Month Calculation in Set Analysis

[rohitraturi]

I have posting dates from 2015, 2016, 2017, and 2018 and I have created a master calendar for the posting dates. I have to calculate the charge amount for the current year/month in the selection and for the prevoius year/month. Also, if I select 2016 then for previous year it should show zero as 2016 is my first year.

I am using the following set analysis syntax but I am getting 0 as my value for every selection

sum(

{<
Year = {"=$Year - 1"}
, Corrected = {'N'}
>} [Amount])
-
sum(
{<
Year = {"=$Year - 1"}
, Corrected = {'Y'}
>} [Amount])

[sunny_talwar · Accepted solution]

May be try this

Sum({<Year = {"$(=Max(Year) - 1)"}, Corrected = {'N'}>} [Amount])
-
Sum({<Year = {"$(=Max(Year) - 1)"}, Corrected = {'Y'}>} [Amount])

[rohitraturi]

Thank You. It worked.

[rohitraturi]

Hi Sunny,

The code you provided is working fine when I select a Year, Quarters, or Months but it gives me zero for the previous year when I pick a custom date range.

[sunny_talwar]

May be ignore selection in the date range?

[rohitraturi]

Is there any way to include the custom date range? I mean using a variable and then create a toggle button to switch between a custom date range and direct selection.

[sunny_talwar]

I might be able to help better if you are able to provide a sample to check this out

[rohitraturi]

Sure, I will share by the EOD.