Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Take last version number value

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?
 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    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
    )

9 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    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
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        Anonymous 

        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
    )