Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
saranmurthy
Frequent Visitor

how to get the dax for Sum of Quote Value based on latest revision and Quote year

Dynamically If I select year 2024 I need Sum of hilighted year which also the latest revision. If I select range from 2023 and 2024 I need whichever is laest in both years and sum.

 

saranmurthy_1-1752055895896.png

 

 

 

19 REPLIES 19
v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

Just wanted to follow up on this. I noticed you're currently using the DAX shared on 16-Jul-2025 to handle dynamic filtering for one year at a time.

Let me know if it's meeting your requirements or if you're still facing any issues happy to help further if needed!

 

Thanks,

Prasanna Kumar

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

Just wanted to follow up on this thread. I noticed you're currently using the DAX shared on 16-Jul-2025 to handle dynamic filtering for one year at a time.

Let me know if it's meeting your requirements or if you're still facing any issues happy to help further if needed!

 

Thanks,

Prasanna Kumar

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

Just following up to see if you had a chance to try creating it as a calculated column instead of a measure. Did that help achieve the result you were looking for?

If you're still facing issues or need further assistance, feel free to share more details  we’re here to help!

 

Thanks & Regards,
Prasanna Kumar

@v-pgoloju as of now i am using the DAX you gave on 16Jul25 as it needs to be dynamic... and  filter only one year at a time. 

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,


Just checking were you trying to create a measure or a calculated column?

If it was a measure, you might want to try creating it as a calculated column instead and see if that gives the desired result. Let us know how it goes or if you’re still running into issues.

 

Thanks & Regards,

Prasanna Kumar

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

were you trying to create a measure or caluculated column?

if you are creating a measure, try creating a calculated column.

Regards,

Prasanna kumar

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

could you please try below Dax measure.

IsLatestRev = 
VAR SelectedYears = CALCULATETABLE(VALUES('Quotes'[Quote Year]), ALLSELECTED('Quotes'))
VAR MaxRev =CALCULATE(MAX('Quotes'[Rev #]),
        FILTER(ALL('Quotes'),
            'Quotes'[Opportunity #] = EARLIER('Quotes'[Opportunity #]) &&
            'Quotes'[Quote Year] IN SelectedYears))
RETURN
IF('Quotes'[Rev #] = MaxRev, 1, 0)

 
Regards,

prasanna Kumar

@v-pgoloju ,

 

Thanks for the updated  mearure... but Earlier functions is not recognizing the opportunity # column...

saranmurthy_0-1752671360261.png

 

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

create a calculated column that will flag only the latest revision per Opportunity based on selected slicer years. apply a visual-level filter for flag 1.

 

IsLatestRevisionAcrossSelectedYears = 
VAR SelectedYears = CALCULATETABLE(VALUES('Quotes'[Quote Year]), ALLSELECTED('Quotes'))
VAR MaxRev =
    CALCULATE(MAX('Quotes'[Rev #]),FILTER(
            ALL('Quotes'),
            'Quotes'[Opportunity #] = EARLIER('Quotes'[Opportunity #]) &&
            'Quotes'[Quote Year] IN SelectedYears))
RETURN
IF('Quotes'[Rev #] = MaxRev, 1, 0)

 

 

Thanks & Regards,

Prasanna Kumar

 

Dear @v-pgoloju ,

 

Thanks for the solution....I just tried with this method, but the problem here is the leatest revision flag "1" is always fixed to the latest revision irrecpective of the year chosen... For example If I have 2 Quotes created each year for the same opportunity (2023-2025)  for 3 years (total 6 Quotes)... and "1" is flagged only to 6th revision in 2025. and If I choose 2023 and 2024 I should be seeing only 4th revision as the latest one and not 6th revision.

 

Thanks and Regards.

Saran

v-pgoloju
Community Support
Community Support

Hi @saranmurthy,

 

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @ThxAlot@DataNinja777  and @FBergamaschi  for prompt and helpful responses.

 

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

 

Best regards,
Prasanna Kumar

@v-pgoloju ,

 

Sorry for my delay in response/accepting the solution...

 

whenever table is created based on Opportunity # as per below screen shot and I apply Filter for both 2024 and 2025, the latest Revisions values for 2024 and 2025 are getting added up...

 

In theory I would need the sum values of the latest Revision when I select 2024 and 2025.

 

For example as per the below screenshot, I have selected 2024 and 2025 and for Opportunity # 165413 the sum value (Rev # 2 and Rev # 3)  appearing as £1,345,260 where as i would need only the latest revision of both year put together which Rev # 3 ( £670,069)

 

 

saranmurthy_1-1752558761062.png

 

 

v-pgoloju
Community Support
Community Support

Hi,

Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve tried to reproduce the scenario using the Dax measure below. Please review and adjust it according to your data source. If the issue still persists, feel free to share more details, and we’ll be happy to assist further.


Best regards,
Prasanna Kumar

Latest Quote Value by Year = 
VAR SelectedYears = VALUES('Quotes'[Quote Year])
RETURN
SUMX( FILTER(ADDCOLUMNS('Quotes', "@MaxRev", 
                CALCULATE(MAX('Quotes'[Rev #]),
                    ALLEXCEPT('Quotes', 'Quotes'[Opportunity #], 'Quotes'[Quote Year]))),
        'Quotes'[Quote Year] IN SelectedYears &&
        'Quotes'[Rev #] = [@MaxRev]),
    'Quotes'[Quote Value in £])
saranmurthy
Frequent Visitor

Hi Sir, Sorry for missing the data...understood the ask...

 

Below data set....

 

Opportunity #Quote NameRev #Quote YearQuote Value in £
651411Q-651411-112023312
651411Q-651411-1120231,412
651411Q-651411-112023421
651411Q-651411-2220241,232,145
651411Q-651411-222024424,141
651411Q-651411-332024248,741
651411Q-651411-442025124,582
651221Q-651221-112023124
651221Q-651221-112023312
651221Q-651221-11202314
651221Q-651221-11202341
651221Q-651221-2220244,152,440
165413Q-165413-112023675,330
165413Q-165413-222024675,201
165413Q-165413-332025670,059
176512Q-176512-112023234,110
176512Q-176512-2220241,672
176512Q-176512-22202421,899
176512Q-176512-222024998
176512Q-176512-2220243,213
176512Q-176512-33202514,412
176512Q-176512-3320251,312
176512Q-176512-332025132

 

ThxAlot
Super User
Super User

ThxAlot_0-1752092867314.png

 

For fun only, to showcase old but still powerful Excel formulas,

ThxAlot_0-1752093008936.png

 



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Hi, Thanks for the Help...., I tried it.....partially working... as I select 2024 and 2025... Its not taking the sum values dynamically... its adding latest revision values for both 2024 and 2025...

DataNinja777
Super User
Super User

Hi @saranmurthy ,

 

You can get the sum of 'Quote Value in £' for the latest revision by creating a DAX measure. This measure will dynamically update based on the years you select in a slicer. Just remember to replace 'YourTable' with the actual name of your data table in the formula.

Sum of Latest Quote Value = 
CALCULATE(
    SUM('YourTable'[Quote Value in £]),
    FILTER(
        'YourTable',
        'YourTable'[Rev #] = 
            CALCULATE(
                MAX('YourTable'[Rev #]),
                ALLEXCEPT('YourTable', 'YourTable'[Opportunity #])
            )
    )
)

This formula works by first using the FILTER function to scan your table and keep only the relevant rows. The logic compares each row's Rev # to the maximum revision number for that specific Opportunity # within the selected year(s). The ALLEXCEPT function is what makes it dynamic; it tells the MAX calculation to respect your year slicer while it finds the highest revision for each opportunity. Finally, the outer CALCULATE and SUM functions add up the quote values for only the rows that FILTER identified as being the latest revision.

 

So, when you select the year 2024, the measure correctly identifies revision 3 as the latest for Opportunity # 651411 within that year and includes only its value. If you select a range of 2023 and 2024, the logic still finds the overall latest revision for each opportunity across both years. For example, with Opportunity # 165413, it would see revisions from both years but correctly choose revision 2 as the latest and use its value in the final sum.

 

Best regards,

Thanks for the Solution.... But when I select the year... i get only the latest Revision and not the latest one for prior year...is it somthing I am doing worng? or did miss somthing..

 

FBergamaschi
Solution Sage
Solution Sage

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

 

Need help uploading data? click here

 

Want faster answers? click here

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.