topic Re: Highest previous value in DAX Commands and Tips

Highest previous value

Anonymous — Tue, 21 Dec 2021 16:25:27 GMT

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

Re: Highest previous value

smpa01 — Tue, 21 Dec 2021 16:27:54 GMT

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

Re: Highest previous value

Anonymous — Tue, 21 Dec 2021 16:43:05 GMT

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

Re: Highest previous value

Anonymous — Tue, 21 Dec 2021 16:47:20 GMT

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

Re: Highest previous value

smpa01 — Tue, 21 Dec 2021 16:49:38 GMT

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

Re: Highest previous value

Anonymous — Tue, 21 Dec 2021 16:49:25 GMT

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

Re: Highest previous value

smpa01 — Tue, 21 Dec 2021 16:51:31 GMT

Anonymous any chance you can post the sample pbix?

Re: Highest previous value

Anonymous — Tue, 21 Dec 2021 16:51:46 GMT

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

Re: Highest previous value

Anonymous — Tue, 21 Dec 2021 16:54:25 GMT

CALCULATE(

[#Orders],

TOPN(

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

Re: Highest previous value

v-cazheng-msft — Fri, 24 Dec 2021 06:37:48 GMT

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!

Re: Highest previous value

Anonymous — Fri, 24 Dec 2021 10:58:18 GMT

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

Re: Highest previous value

v-cazheng-msft — Wed, 29 Dec 2021 08:08:02 GMT

Hi Anonymous

Please try the following Measure.

higest previous order mth =

VAR midT =

TOPN (

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!