cancel
Showing results for
Search instead for
Did you mean:
Member

## 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 last year. I am trying to calculate the % between any of these two periods. The data is set out below. Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## Re: Value between 2 dates

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)```

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User

## Re: Value between 2 dates

Hi @Absalon29,

Download the file from here.

8 REPLIES 8
Super Contributor

## Re: Value between 2 dates

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)```

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User

## Re: Value between 2 dates

Hi @Absalon29,

Download the file from here.

Member

## Re: Value between 2 dates

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

Highlighted
Super User

## Re: Value between 2 dates

You are most welcome.

Member

## Re: Value between 2 dates

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

Member

## Re: Value between 2 dates

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.

Member

## Re: Value between 2 dates

Finally figured it out! Thanks.

Member

## Re: Value between 2 dates

Hi Phil,

Any chance you can show how I would adjust to fact in year to date and since inception dates.

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 284 members 2,962 guests
Recent signins:
Please welcome our newest community members: