Last 12 months with multiple IF conditions

[Anonymous]

Hello all,

I am trying to calculate last 12 months total with 2 conditions. I have attached a dummy table of how my output should look like. When my Area Code contains BU it should only take sum of last 4 rows (row highlighted in orange) or when the profit centre is CF10 it should take sum of last 4 rows i.e fiscal period 12-9-6-3 for both the cases else it should do previous 12 rows addition (row highlighted in green.

I have tried a formula previously suggested to me on the PBI community, which works well for one clause in IF i.e if I take BU case but when I try to put an OR condition in it and insert the CF 10 bit it is not working.

Query that I have been using - 

LTM Column =
IF(SEARCH("BU",'P&L Base'[ru],1,0) <> 0 || 'P&L Base'[profit_center] = "CF10" ,
IF(
[YearMonth]>=201912,
var ym = [YearMonth]
var tab =
TOPN(
4,
CALCULATETABLE(
DISTINCT('P&L Base'[YearMonth]),
FILTER(
ALL('P&L Base'),
[RU]=EARLIER('P&L Base'[RU])&&
[Account_Tag]=EARLIER('P&L Base'[Account_Tag])&&
[profit_center] = EARLIER('P&L Base'[profit_center])&&
[YearMonth]<= ym
)
),
[YearMonth]
)
return
CALCULATE(
SUM('P&L Base'[Total_USD_Amt]),
FILTER(
ALLEXCEPT('P&L Base','P&L Base'[RU],'P&L Base'[Account_Tag],'P&L Base'[profit_center]),
[YearMonth] in tab
)
)
),
IF(
[YearMonth]>=201912,
var ym = [YearMonth]
var tab =
TOPN(
12,
CALCULATETABLE(
DISTINCT('P&L Base'[YearMonth]),
FILTER(
ALL('P&L Base'),
[RU]=EARLIER('P&L Base'[RU])&&
[Account_Tag]=EARLIER('P&L Base'[Account_Tag])&&
[profit_center] = EARLIER('P&L Base'[profit_center])&&
[YearMonth]<= ym
)
),
[YearMonth]
)
return
CALCULATE(
SUM('P&L Base'[Total_USD_Amt]),
FILTER(
ALLEXCEPT('P&L Base','P&L Base'[RU],'P&L Base'[Account_Tag],'P&L Base'[profit_center]),
[YearMonth] in tab
)
)
))

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

Please try to write the below calculated measure.

Last 12 Months Amount Total =
IF (
SELECTEDVALUE ( 'P&L'[Fiscal Period] ) = 12,
CALCULATE (
SUM ( 'P&L'[Amount] ),
ALLEXCEPT ( 'P&L', 'P&L'[Profit Centre], 'P&L'[Area Code], 'P&L'[Fiscal Year] )
),
BLANK ()
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Hi @Jihwan_Kim, thanks for the prompt reply. The dax column query which I have created is working well for last 12 months. It is also working fine if I include an IF condition where my Area Code column contains values starting with "BU". It is just when I want to include one more condition i.e Profit Centre = "CF10", it is not taking this condition into consideration.