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!

v-cazheng-msft's avatar
v-cazheng-msft
Community Support
4 years ago

Hi Anonymous 

 

May I know whether your issue has been resolved? If you still have problem on it, could you please show me your #Orders Measure and let me know how many tables in your model and the relationships among them? Thanks in advance!

 

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

    Thanks for your response. this is my my order measure 

     

    #Orders =
    DISTINCTCOUNT('tbl_orders'[order_id]) + 0
     
    and the only relationship is with the dimDate table on order_Date and date