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!

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

     

    Whenever i have 0 as a value for a month for each customer then i dont get the highest previous month using my own solution. However if the currenth month sales is greater than zero then it works . See below 

     

     

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  any chance you can post the sample pbix?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago
        CALCULATE(
        [#Orders],
        TOPN(
        1,
        FILTER(
        SUMMARIZE(
        ALL(dimDate),
        dimDate[Month], dimDate[MonthYearNumber]
        ),
        dimDate[MonthYearNumber] <= MIN(dimDate[MonthYearNumber])
        ), [#Orders], DESC
        )
        )
         
         
         
        The above is the dax code i am currently using but doesnt work if the measure #orders returns zero for the current month like in the previously shared screenshot