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

[saranmurthy]

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.

[v-pgoloju]

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]

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

[saranmurthy]

@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]

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]

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]

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

[saranmurthy]

@v-pgoloju ,

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

[v-pgoloju]

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

[saranmurthy]

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]

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

[saranmurthy]

@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)

[v-pgoloju]

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]

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

Below data set....

Opportunity #	Quote Name	Rev #	Quote Year	Quote Value in £
651411	Q-651411-1	1	2023	312
651411	Q-651411-1	1	2023	1,412
651411	Q-651411-1	1	2023	421
651411	Q-651411-2	2	2024	1,232,145
651411	Q-651411-2	2	2024	424,141
651411	Q-651411-3	3	2024	248,741
651411	Q-651411-4	4	2025	124,582
651221	Q-651221-1	1	2023	124
651221	Q-651221-1	1	2023	312
651221	Q-651221-1	1	2023	14
651221	Q-651221-1	1	2023	41
651221	Q-651221-2	2	2024	4,152,440
165413	Q-165413-1	1	2023	675,330
165413	Q-165413-2	2	2024	675,201
165413	Q-165413-3	3	2025	670,059
176512	Q-176512-1	1	2023	234,110
176512	Q-176512-2	2	2024	1,672
176512	Q-176512-2	2	2024	21,899
176512	Q-176512-2	2	2024	998
176512	Q-176512-2	2	2024	3,213
176512	Q-176512-3	3	2025	14,412
176512	Q-176512-3	3	2025	1,312
176512	Q-176512-3	3	2025	132

[ThxAlot]

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

[saranmurthy]

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]

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,

[saranmurthy]

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]

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