How to get previous fiscal Year max valid from date price

[kunkumnaveen]

Hello All,

I need to show max valid from of each fiscal year irrespective of selected fiscal year

So in a line chart i took

Dimension :Fiscal Year

Expression:

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>}BFVALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR], PROD_HIER4_Name>BFVALIDFROM), Avg({<[FISCAL YEAR]>}BF_PRICE_MAT)),[FISCAL YEAR],BFVALIDFROM,PROD_HIER4_Name)

)

Which is Working Great

But i just find out the above expression will work  fine only if it has  Valid From date  in each fiscal year.

In above case Fiscal year 2017 is showing no value , because there is no valid From  in Fiscal year -2017,but according  to the data

fiscal year -2017 should have price 2043.91 Fiscal year 2016 price ,

because the max Valid From of Fiscal Year -2016 is from

                      Valid From              Valid To                        Price                        FY

So logically i need to show 2043.91 in FY17 as Well, but my current wont work in that way.

What i was thinking is if in any  Fiscal year, if there is no valid From date value then it has to pick last fiscal year max Valid from date price. !

but i have no clue how to write this expression,

Kindly help me in achieving this requirement

PFA sample data