Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |