Forum Discussion
previous month and column filter
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
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
1 Reply
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