cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## 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
Employee

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
Helper III

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

Employee

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors