Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Highest previous value

I want a dax code to return the highest value of previous sales on a monthly basis. E.g see below    Month Sales This month Highest ever sales Jan 1,000 1,000 Feb 0 1,000 March 2...
DAX
power bi
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous 

     

    Please try the following Measure.

    higest previous order mth =

    VAR midT =

        TOPN (

            1,

            FILTER (

                SUMMARIZE (

                    ALL ( DimTable ),

                    DimTable[Month],

                    DimTable[MonthYearNumber],

                    "val", SUMX ( DimTable, [#Orders] )

                ),

                DimTable[MonthYearNumber] <= MIN ( DimTable[MonthYearNumber] )

            ),

            [val], DESC

        )

    return MAXX ( midT, [val] )

     

    Then, the result should look like this.

     

    For more details, please refer to the attached pbix file.

     

    Best Regards,

    Community Support Team _ Caiyun

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

Is Sales a measure or values in your table.

Also, can you share sample data and data model ( Which table does the store value come from).

 

Regards,

Harsh Nathani

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Its a measure . it is a distinct count of order number. Problem is i kind of have a dax code that works but any months that the order count is zero then it doesnt return corresponding value of the highest previous month