Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
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
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?
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
60 | |
58 | |
54 | |
36 | |
33 |
User | Count |
---|---|
79 | |
66 | |
45 | |
45 | |
43 |