Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Max amount

Hey guys I am trying to create a Dax measure that will give me the MAX amount by the Max month, I want that measure to dynamic. For example if there is a new month, i need that measure to dynamically change. Here is a example:

Month    Amt

Jan          100

Feb          300

Mar         2000

 

i would like the score card show 2000 (which reflects mar) without anything being select.  

  • daxer-almighty's avatar
    daxer-almighty
    4 years ago
    // First of all, you have to
// be able to determine which
// month is the last one. To do
// that you need to number your
// months. Say you've created
// a column called MonthConsecutiveNumber.
// Then, to always get the amount for the
// very last month, you write:

[Last Month Amount] =
    SUMX(
        TOPN(1,
            ALL( T ),
            T[MonthConsecutiveNumber],
            DESC
        ),
        T[Amt]
    )

2 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    4 years ago

    Hi Anonymous ,

     

    Try this measure:

     

    Measure= CALCULATE(SUM(Table[Amt]), FILTER(Table, MAX(Table[Date]))

     

     

    Mark this as a solution if I answered your question. Kudos are always appreciated.

    Thanks

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    4 years ago
    // First of all, you have to
// be able to determine which
// month is the last one. To do
// that you need to number your
// months. Say you've created
// a column called MonthConsecutiveNumber.
// Then, to always get the amount for the
// very last month, you write:

[Last Month Amount] =
    SUMX(
        TOPN(1,
            ALL( T ),
            T[MonthConsecutiveNumber],
            DESC
        ),
        T[Amt]
    )