Forum Discussion
Value between 2 dates
I need to calculate a measure which will always give me the most up todate value (the max date) for the NAV divided by the NAV on the a number of date ranges eg the last month, the last 3 months, the...
Hi Anonymous
Here is one approach to consider as a calculated measure. Just replace where I have Table 3 with your own table and set the format to Percent.
Measure 2 = VAR MonthsToLookBack = 3 VAR maxDate = MAX('Table 3'[Date]) VAR otherDate = CALCULATE(MAX('Table 3'[Date]),FILTER('Table 3','Table 3'[Date] < DATEADD('Table 3'[Date],MonthsToLookBack,MONTH))) VAR LatestNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=maxDate)) VAR otherNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=otherDate)) RETURN DIVIDE (LatestNAV - otherNAV,LatestNAV)
Hi Anonymous
Here is one approach to consider as a calculated measure. Just replace where I have Table 3 with your own table and set the format to Percent.
Measure 2 =
VAR MonthsToLookBack = 3
VAR maxDate = MAX('Table 3'[Date])
VAR otherDate = CALCULATE(MAX('Table 3'[Date]),FILTER('Table 3','Table 3'[Date] < DATEADD('Table 3'[Date],MonthsToLookBack,MONTH)))
VAR LatestNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=maxDate))
VAR otherNAV = CALCULATE(MAX('Table 3'[Nav]),FILTER('Table 3',[Date]=otherDate))
RETURN DIVIDE (LatestNAV - otherNAV,LatestNAV)
Hi Phil, I copied this over to my model and the measure isn't producing any results. If I have to include Year to date and since inception could you explain what alternations I need to make to your measure