Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi Anonymous,

     

    Download the file from here.

     

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Anonymous,

 

Download the file from here.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ashish, Thanks so much for you help. I'm getting there but keep hitting dax roadblocks;your input is invaluable and much appreciated. Thanks. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are most welcome.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Ashish, I am struggling a bit to implement your solution which works for periods like 1 month, 3 months or 12 months but how could I factor in Year to date and since inception calculations using your model.