2 Replies Latest reply on Aug 30, 2018 4:03 AM by Sourabh Dasgupta

LOD - Show Last Year Value when select "Filter to Latest Date" on filter

Hi Folks

My Goal : Create a calculated field which shows the last year value even though the application has selected "Filter to latest date value when workbook is opened" on filter.

I am struggling in LOD where i have set the filter on date so that it automatically selects the latest Year when open the application

Then i have one calculated field where it sum all value of last year

i use the formula    -      { FIXED : sum(if Year= year(Date)-1 then Value end) }

But No luck , it won't show the last year value

Unless i select 2017, but it is not expected to click 2017 while it is supposed to show the 2017 value  without select 2017 on Year(Date)

The original dataset

• 1. Re: LOD - Show Last Year Value when select "Filter to Latest Date" on filter

Hi Alexndra ,

Create a calculated field like below

Last Year : {FIXED : sum(if Year={MAX(year([Date]))} -1 then Value end)}

It will give correct Output

FINAL VIEW:

Hope this helps

BR,

NB

• 2. Re: LOD - Show Last Year Value when select "Filter to Latest Date" on filter

Hello Alexandra, when you are filtering on "2018", the data in consideration does not have any records for "2017", hence the calculation does not return any value for 2017. You will have to apply an alternate solution without using any filters. Create two calculated fields as below:

1. Current Year value = IF YEAR([Order Date]) = YEAR(TODAY()) THEN [Sales] END

2. Last Year value = IF YEAR([Order Date]) = YEAR(TODAY()) - 1 THEN [Sales] END

Use these calculated fields in the view without keeping the years in the filter. I hope this helps.