cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Hi @Voose,

 

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

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

View solution in original post

2 REPLIES 2
Voose
Helper III
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

 

 

Hi @Voose,

 

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors