Forum Discussion

shihwenwu's avatar
shihwenwu
Frequent Visitor
8 years ago
Solved

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(Mon...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

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