Take last version number value

[sibeck_77]

I want to make a report that shows only the value that is evident in the last version (yellow marked).

I tried it with the follow DAX expressions:

Last Version No = CALCULATE(SUM(Verkaufsoffertenarchiv[Amount]),LASTNONBLANK(Verkaufsoffertenarchiv[Version_No],Verkaufsoffertenarchiv[Version_No]))

or

Max Version No =

MAXX(

    KEEPFILTERS(VALUES('Verkaufsoffertenarchiv'[Version_No])),

    CALCULATE(SUM('Verkaufsoffertenarchiv'[Version_No]))

)

Both expressions are not the solutions because I get the sum from all versions instead of showing only the latest value. 

I have the following columns available:

 Could someone help me to reach this goal?

[Fowmy]

@sibeck_77 

Try the following measure:

Last Version No = 
var __no = MAX(Verkaufsoffertenarchiv[Version_No])
var __maxno = 
    MAXX( ALLEXCEPT( Verkaufsoffertenarchiv , Verkaufsoffertenarchiv[No] ) , Verkaufsoffertenarchiv[Version_No] ) 
return
    CALCULATE(
        SUM(Verkaufsoffertenarchiv[Amount]),
        Verkaufsoffertenarchiv[Version_No] = __maxno,
        Verkaufsoffertenarchiv[No] = __no
    )

[sibeck_77]

@Fowmy  

Thanks for your quick response. Unfortunately, there is an error message:

Error Message:
MdxScript(Model) (12, 9) Calculation error in Measure 'Sales Offer Archive'[Last Version No]: DAX comparison operations do not support comparisons between values of type text and integer. You can use the VALUE function or the FORMAT function to convert one of the values.

Do you know what I have to change that your DAX expression is working?

[Fowmy]

@sibeck_77 

Can you try now?

Last Version No = 
var __no = MAX(Verkaufsoffertenarchiv[No])
var __maxno = 
    MAXX( ALLEXCEPT( Verkaufsoffertenarchiv , Verkaufsoffertenarchiv[No] ) , Verkaufsoffertenarchiv[Version_No] ) 
return
    CALCULATE(
        SUM(Verkaufsoffertenarchiv[Amount]),
        Verkaufsoffertenarchiv[Version_No] = __maxno,
        Verkaufsoffertenarchiv[No] = __no
    )

[sibeck_77]

@Fowmy 

Unfortunately it does not display any values:

Do you have any other ideas?

[Fowmy]

@sibeck_77 

I created the same scenario and tested. 

Last Version No M = 
var __no = MAX(Verkaufsoffertenarchiv[No])
var __maxno = 
    MAXX( ALLEXCEPT( Verkaufsoffertenarchiv , Verkaufsoffertenarchiv[No] ) , Verkaufsoffertenarchiv[Version_No] ) 
return
    CALCULATE(
        SUM(Verkaufsoffertenarchiv[Amount]),
        Verkaufsoffertenarchiv[Version_No] = __maxno,
        Verkaufsoffertenarchiv[No] = __no
    )

My Table:

Result:

[sibeck_77]

@Fowmy 

In my tests the result is that it takes the largest version no. and shows the amount just of this No. But it should be that it shows the amount of the largest version no. of every No.  

Do you know what I mean?
In your test result it seems to be working... so I do not know what is wrong in my table.

[Fowmy · Accepted solution]

@sibeck_77 

Please try this one:

Last Version No M = 
var __no = MAX(Verkaufsoffertenarchiv[No])
var __maxno =     
    MAXX( FILTER(  ALL(Verkaufsoffertenarchiv) ,  Verkaufsoffertenarchiv[No] = __no ) , Verkaufsoffertenarchiv[Version_No] )
return
    CALCULATE(
        SUM(Verkaufsoffertenarchiv[Amount]),
        Verkaufsoffertenarchiv[Version_No] = __maxno,
        Verkaufsoffertenarchiv[No] = __no
    )

[sibeck_77]

Dear @Fowmy 

I have a follow-up question.

When I create a table with your DAX, the total appears with 0. Do you know how this value also still shows the totals?

[sibeck_77]

@Fowmy  

Please excuse the long response time due to vacation absence.
Thanks for the adjustment, so it worked now.
Thanks again - awsome support!