Highest previous value

[Anonymous]

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,000	2,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 

[v-cazheng-msft · Accepted solution]

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]

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]

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 

[harshnathani]

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]

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 

[smpa01]

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

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

[Anonymous]

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]

@Anonymous  any chance you can post the sample pbix?

[Anonymous]

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

[smpa01]

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