Find the NAV which is past 1 year from the latest NAV date

[alearner]

Need some suggestion. I have 2 tables:

1. Date Table which has all the dates

2. NAV table which has NAV for a Scheme for the past 1o years. The columns are as below:

     NAV_Date Scheme_Name NAV_Value

My requirement is that I would like to know what was the NAV 1 year from the lastest NAV in the NAV Table. 

The challange that I am facing is that if I substract 1 year to the lastest NAV that might be a Saturday or Sunday or a Holiday and I will not have a NAV_Value for that date.

[Anonymous · Accepted solution]

Hi @alearner ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column.

Column = 
var _b=DATE(YEAR('NAV table'[navDate])-1,MONTH('NAV table'[navDate]),DAY('NAV table'[navDate]))
var _c=MinX(FILTER('NAV table',[navDate]>=_b),[navDate])
RETURN IF(not YEAR('NAV table'[navDate])-1 in VALUES('Date Table'[Date].[Year]),BLANK(),CALCULATE(SUM('NAV table'[navValue]),FILTER('NAV table','NAV table'[navDate]=_c)))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[alearner]

Please refer below for the sample file:

pastebin.com/embed_js/0EcLuH32?theme=dark 

The last row in the sample data is 

1. 20-10-2023 250.429 250.429 HDFC Mutual Fund HDFC Large and Mid Cap Fund - Direct Plan - Growth

So the latest NAV date is 20-10-2023 for HDFC Large and Mid Cap Fund - Direct Plan - Growth scheme. I would like to know the NAV value for 20-10-2022. Since 20-10-2022 was a weekday I have a value but had it been a weekend I would not have a value so intead would lile to have the value for Monday or the next day which has a value. 

[Fowmy]

@alearner 

It will be helpful if you could provide some sample data and explain the expected results.