topic Re: Need help to build DAX to get the sum of the latest version. in DAX Commands and Tips

Need help to build DAX to get the sum of the latest version.

RJMonteza — Mon, 23 Jan 2023 16:00:00 GMT

I have 5 columns:

1. Quote Number (seen as 123456)

2. Sales

3. Quote Number / Version (seen as "123456:1" being 1 as the number of version)

4. Period

5. Owner

What I need is to get the total sales per period of the latest version of the Quote with selected Owners only. There are more than 20 owners but I only need 10 of them. (with specific names)

I had done some experiments but I can't get it through.

Thanks!

Re: Need help to build DAX to get the sum of the latest version.

FreemanZ — Mon, 23 Jan 2023 16:15:04 GMT

hi RJMonteza

not sure if fully get you, you may try like:

Measure = VAR _table = ADDCOLUMNS( SUMMARIZE( TableName, TableName[Quote Number], TableName[Period] ), "LatestQuoteVersion", CALCULATE(MAX(TableName[uote Number / Version])) ) RETURN CALCULATE( SUM(TableName[Sales]), TREATAS( _table, TableName[Quote Number], TableName[Period], TableName[Quote Number / Version] ) )

Re: Need help to build DAX to get the sum of the latest version.

RJMonteza — Tue, 24 Jan 2023 17:25:32 GMT

Hello FreemanZ,

I created a table to further explain:

I need the sum of the highlighted cells. I'm struggling to catch it as I do not have a date. I only have the Quote/Version.

Re: Need help to build DAX to get the sum of the latest version.

Anonymous — Wed, 25 Jan 2023 05:56:09 GMT

Hi RJMonteza ,

Please try below dax formula:

1. Below is my test table

Table:

2. Create a measure with below dax formula

Measure = VAR _a = ADDCOLUMNS ( 'Table', "Version", RIGHT ( 'Table'[Quote/Version], LEN ( 'Table'[Quote/Version] ) - LEN ( 'Table'[Quote Number] ) - 1 ) ) VAR _b = SUMX ( FILTER ( _a, [Version] = MAXX ( FILTER ( _a, [Quote Number] = EARLIER ( 'Table'[Quote Number] ) ), [Version] ) ), [Sales] ) RETURN IF ( ISINSCOPE ( 'Table'[Quote Number] ), SUM ( 'Table'[Sales] ), _b )

3. Add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need help to build DAX to get the sum of the latest version.

RJMonteza — Mon, 30 Jan 2023 12:37:48 GMT

Hello!

I think I'm getting there. I'm trying to add a filter to include only the names that needed to sum up the sales. Example:

Measure =

VAR _a =

ADDCOLUMNS (

'Table',

"Version",

RIGHT (

'Table'[Quote/Version],

LEN ( 'Table'[Quote/Version] ) - LEN ( 'Table'[Quote Number] ) - 1

)

VAR _b =

SUMX (

FILTER (

_a,

[Version]

= MAXX (

FILTER ( _a, [Quote Number] = EARLIER ( 'Table'[Quote Number] ) ),

[Version]

)

[Sales]

)

RETURN

CALCULATE(

IF ( ISINSCOPE ( 'Table'[Quote Number] ), SUM ( 'Table'[Sales] ), _b ),

'Table'[TSE] IN {"JOHN","DAVID","CINDY","JESSICA","MICHAEL","JOAN","MAY","NICOLE","MARY"})

This is somehow working on the other measures but I think it's not working here. The sum is not changing. I also tried to create VAR _C to filter these names like:

VAR _c =

CALCULATE(_b,

FILTER('Table',CONTAINS('Table','Table'[TSE], "JOHN",'Table'[TSE],"DAVID",'Table'[TSE],"CINDY",'Table'[TSE],"JESSICA",'Table'[TSE],"MICHAEL",'Table'[TSE],"JOAN",'Table'[TSE],"MAY",'Table'[TSE],"NICOLE",'Table'[TSE],"MARY")

RETURN

IF ( ISINSCOPE ( ''TABLE'[QUOTE NUMBER] ), SUM ( 'TABLE'[SALES] ), _c)

But I'm gettin an error that my syntax for return is incorrect.