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