cancel
Showing results for
Did you mean:
Member

## Pulling back a max date through

Hi All,

Bit confused on this one and apsologies if my explanation is lacking but here goes.

I have an object (LIC) that holds my information and I want to pull the highest of the dates associated with this object.

For example

LIC - SCL

There is a 1 to many relationship between LIC and SCL.

In excel I think the formula I'm trying to do looks like

{=MAX(IF(\$D:\$D=D2,\$I:\$I))}

basically where the ID of LIC matches pull through the highest date which is what I want to do here.

Second Step:

Where the highest Date matches I also want to pull through another Field (the Quantity) - normally I could just use a Vlookup here to work this out but unsure how DAX would work?

Any Ideas guys?

Thanks

Voose

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Pulling back a max date through

Hi @Voose,

Could you try the formula below to see if it works in your scenario?

```Amount =
VAR latestDate =
CALCULATE (
MAX ( 'Service Contract Line'[Original_End_Date__c] ),
ALLEXCEPT ( 'Licence Schedule', 'Licence Schedule'[Licence Name] )
)
RETURN
CALCULATE (
SUM ( [Amount] ),
'Service Contract Line'[Original_End_Date__c] = latestDate
)
```

Regards

2 REPLIES 2
Member

## Re: Pulling back a max date through

Ok I've managed to work out the step above using a measure:

Latest Date = calculate(max('Service Contract Line'[Original_End_Date__c]),ALLEXCEPT('Licence Schedule','Licence Schedule'[Licence Name]))

What I need now is to work out the amount that relates to the same date and licence number as pulled above from the Service Contract Line Object into the Licence Schedule object

Thoughts guys?

Voose

Edit - The issue I have is I can't just use the Sum function as it will just add all of the amounts together across all of the years, I just want to display the latest years amount i.e.

year 1 = 1000

Year 2 = 2000

Year 3 = 3000

I want to just show Amount = 3000

Atm if I use the sum function I get amount = 6000

Super Contributor

## Re: Pulling back a max date through

Hi @Voose,

Could you try the formula below to see if it works in your scenario?

```Amount =
VAR latestDate =
CALCULATE (
MAX ( 'Service Contract Line'[Original_End_Date__c] ),
ALLEXCEPT ( 'Licence Schedule', 'Licence Schedule'[Licence Name] )
)
RETURN
CALCULATE (
SUM ( [Amount] ),
'Service Contract Line'[Original_End_Date__c] = latestDate
)
```

Regards

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 316 members 3,640 guests
Recent signins: