Forum Discussion

Voose's avatar
Voose
Helper III
9 years ago
Solved

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 associ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Voose,

     

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

    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

Voose's avatar
Voose
Helper III
9 years ago

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

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Voose,

 

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

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