3 Replies Latest reply on Jun 22, 2017 8:14 PM by sunil.sachdeva

# How to show PY YTD based on available YTD date

I have a worksheet with number of transactions for last year full year by date, and this year up to yesterday. The current year data will get update from time to time when needed.

I want to build a stacked bar chart (stack by different transaction types), with LY YTD a bar, and CY YTD another bar. LY YTD would change based on available day in CY. Say if CY I have data up to 23 Jun, I would like to have PY YTD also show up to 23 Jun 2016.

Thanks a lot!

Regards,

Cindy

• ###### 1. Re: How to show PY YTD based on available YTD date

Hi,

Something like this:

Created a calculated column:

IF YEAR([Fiscal year/period])=YEAR({FIXED :MAX([Fiscal year/period])}) THEN 'CY'

ELSEIF YEAR([Fiscal year/period])=YEAR({FIXED :MAX([Fiscal year/period])})-1

AND  [Fiscal year/period] <= DATEADD('month',-12,{FIXED :MAX([Fiscal year/period])} ) THEN 'PY'

ELSE 'More than last 2 years'

END

Please find attached sample workbook, hope that helps.

• ###### 2. Re: How to show PY YTD based on available YTD date

Hi sunil.sachdeva,

Thanks for your help! But my data only has two years, 2016 and 2017, why the result has huge # of Transactions in 'More than last 2 years'?

Regards,

Cindy

• ###### 3. Re: How to show PY YTD based on available YTD date

Hi Cindy,

'More Than last 2 years' is just a label for left over transactions for prior year.

Your data has transactions till '01-06-2017'

So CY is from '01-01-2017' to '01-06-2017'

and PY is from '01-01-2016' to '01-06-2016'

Left over transactions from '02-06-2016' to '31-12-2016' is labelled as 'More than last 2 years', you can change label to any reasonable label or even filter out data, if you want.

Thanks,

Sunil