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 

 

MonthSales This monthHighest ever sales
Jan1,0001,000
Feb01,000
March2,0002,000

 

In the above table, when in January sales this month is 1000 and the highest ever was 1,000 and in February our highest sales ever still remains 1000 despite selling nothing for that month. However, in March the figure changed because the march sales has beaten that of January. Please I need a DAX to return this. 

 

amitchandak VahidDM AlexisOlson 

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!

11 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  you can either create a calculated column like this

     

    Column = 
var _monthIndex = Calculate(MAX('Table'[MonthIndex]))
return MAXX(FILTER(ALL('Table'),'Table'[MonthIndex]<=_monthIndex),'Table'[Sales])

     

     

    or a measure like this

    Measure = 
var _monthIndex = MAX('Table'[MonthIndex])
return MAXX(FILTER(ALL('Table'),'Table'[MonthIndex]<=_monthIndex),'Table'[Sales])

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks for your response. it didnt work for me. when i drag in each store

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous  provide sample pbix or put the screenshot of the error here.

  • 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 

  • 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 
  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    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!