previous month and column filter

[shihwenwu]

Hi,

I want to create a new column "pre_ops", which get "order product sales" of the previous month (by Yr_month) based on "account".

Here is my solution:

pre_ops = CALCULATE(MAX(Month_over_Month[order_product_sales]),FILTER(Month_over_Month,Month_over_Month[account]=EARLIER(Month_over_Month[account])),PREVIOUSMONTH(Month_over_Month[Yr_month]))

However, the reason I am using "MAX" is only because I have no idea which function to use to get a value in other column in the same table.

Since in this case there is only one "order product sales" in each month in each account, I can use "MAX" as a way out.

Wondering if there are more elegant solution for this kind of issue.

Thx

[v-frfei-msft · Accepted solution]

Hi @shihwenwu,

You can use SUM() function instead of MAX() function, which also works if you have more than one "order product sales" in each month for  each account.

pre_ops = CALCULATE(SUM(Month_over_Month[order_product_sales]),FILTER(Month_over_Month,Month_over_Month[account]=EARLIER(Month_over_Month[account])),PREVIOUSMONTH(Month_over_Month[Yr_month]))

For more details, please check the pbix as attached.

https://www.dropbox.com/s/nzaumqewiqvksl2/previous%20month%20and%20column%20filter.pbix?dl=0

Regards,

Frank