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

[RJMonteza]

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!

[Anonymous]

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.

[RJMonteza]

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.

[FreemanZ]

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

[RJMonteza]

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.