topic Re: how to get the dax for Sum of Quote Value based on latest revision and Quote year in DAX Commands and Tips

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

saranmurthy — Wed, 09 Jul 2025 10:12:58 GMT

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.

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

FBergamaschi — Wed, 09 Jul 2025 13:41:14 GMT

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

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

DataNinja777 — Wed, 09 Jul 2025 13:50:45 GMT

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,

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

ThxAlot — Wed, 09 Jul 2025 20:30:48 GMT

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

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

saranmurthy — Thu, 10 Jul 2025 07:04:50 GMT

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...

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

saranmurthy — Thu, 10 Jul 2025 09:09:30 GMT

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..

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

saranmurthy — Thu, 10 Jul 2025 09:13:43 GMT

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

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

Anonymous — Sat, 12 Jul 2025 04:55:31 GMT

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 £])

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

Anonymous — Tue, 15 Jul 2025 05:06:12 GMT

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

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

saranmurthy — Tue, 15 Jul 2025 05:58:53 GMT

Anonymous ,

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)

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

Anonymous — Tue, 15 Jul 2025 08:28:07 GMT

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

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

saranmurthy — Tue, 15 Jul 2025 12:24:17 GMT

Dear Anonymous ,

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

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

Anonymous — Wed, 16 Jul 2025 07:31:11 GMT

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

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

saranmurthy — Wed, 16 Jul 2025 13:09:28 GMT

Anonymous ,

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

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

Anonymous — Thu, 17 Jul 2025 05:31:53 GMT

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

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

Anonymous — Tue, 22 Jul 2025 03:26:50 GMT

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

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

Anonymous — Thu, 24 Jul 2025 13:32:23 GMT

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

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

saranmurthy — Tue, 29 Jul 2025 05:32:07 GMT

Anonymous 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.

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

Anonymous — Fri, 01 Aug 2025 04:43:06 GMT

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

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

Anonymous — Thu, 07 Aug 2025 04:56:07 GMT

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