Get the value from a column based on a filter in another column

[Anonymous]

Hello, 

I am trying to create a measure to get the sales value of two columns in another table that is linked to my fact table (previous month and previous scenario), so in the example below, for key 0 (M1-1) the value I need to fetch is (M12-2) which is 200. I have tried multiple solutions but none gave me a result. Last I tried was the below:

Sell Previous Month =

   SUMX(

      FILTER(

         fSales,

         fSales[Month] = RELATED(DimMonth[Previous_Month])

      ),

      SUM(fSales[Sales])

   )

Key	Month	Scenario	Sales	Sales_Previous_Month
0	M1	1	100	200
1	M2	1	500
2	M3	1	600
3	M4	1	200
4	M5	1	400
5	M1	2	150
6	M2	2	150
7	M3	2	300
8	M4	2	150
9	M5	2	200
10	M6	2	200
11	M7	2	200
12	M8	2	200
13	M9	2	200
14	M10	2	200
15	M11	2	200
16	M12	2	200

Key	Month	Scenario	Previous_Month	Previous_Scenario
0	M1	1	M12	2
1	M2	1	M1	1
2	M3	1	M2	1
3	M4	1	M3	1
4	M5	1	M4	1
5	M1	2	NA	NA
6	M2	2	M1	NA
7	M3	2	M2	NA
8	M4	2	M3	NA
9	M5	2	M4	NA
10	M6	2	M5	NA
11	M7	2	M6	NA
12	M8	2	M7	NA
13	M9	2	M8	NA
14	M10	2	M9	NA
15	M11	2	M10	NA
16	M12	2	M11	NA

Thank you

[Anonymous · Accepted solution]

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

[Anonymous]

hi @Anonymous , I did a few modifications on the equation above and the logic more or less works. One issue I face is that I noticed I get no values when the value of the month itself does not exist. For example in the table below I have no value for M2 Scenario one, so for some reason the previous month also comes out with no value although it should still show 100. What could be the reason for that?

Key	Month	Scenario	Sales	Sell Previous Month
0	M1	1	100	200
1	M2	1
2	M3	1	600	500
3	M4	1	200	600
4	M5	1	400	200
5	M1	2	150
6	M2	2	150
7	M3	2	300
8	M4	2	150
9	M5	2	200

[Anonymous]

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!

[Anonymous]

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!