Forum Discussion
Get the value from a column based on a filter in another column
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Sell Previous Month = var _m= MAXX( FILTER(ALL(DimMonth),'DimMonth'[Key]=EARLIER('fSales'[Key])&&'DimMonth'[Month ]=EARLIER('fSales'[Month])),[Previous_Month]) var _Scenario= MAXX( FILTER(ALL(DimMonth),'DimMonth'[Key]=EARLIER('fSales'[Key])&&'DimMonth'[Month ]=EARLIER('fSales'[Month])),[Previous_Scenario]) return MAXX( FILTER(ALL('fSales'), 'fSales'[Month]=_m&&'fSales'[Scenario]=_Scenario),[Sales])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Sell Previous Month =
var _m=
MAXX(
FILTER(ALL(DimMonth),'DimMonth'[Key]=EARLIER('fSales'[Key])&&'DimMonth'[Month ]=EARLIER('fSales'[Month])),[Previous_Month])
var _Scenario=
MAXX(
FILTER(ALL(DimMonth),'DimMonth'[Key]=EARLIER('fSales'[Key])&&'DimMonth'[Month ]=EARLIER('fSales'[Month])),[Previous_Scenario])
return
MAXX(
FILTER(ALL('fSales'),
'fSales'[Month]=_m&&'fSales'[Scenario]=_Scenario),[Sales])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous, thanks a lot this indeed works but there is one step I forgot to mention. So this does give me the previous month-scenario sales but there are many other unique keys in the table (products), so what it does is actually brings the value from previous month for another product, how can I include the product key information in the code above. Thank you!
actually I managed to add all the row key information in the last filter as done with the two variables above. All good now! Thanks a lot!