Nested IF -Finanacial Templates

DAX DAX Calculations
1

Hi There,
I created a measure(Last Year BAL) to loop through the variables I have defined, where the value exists it should calculate the value (LY) - Defined as another measure;
Last Year Bal =
VAR DemandDeposits = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Demand Deposits”))
VAR Collections = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Collections/Remittances”))
VAR Bal = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Balances with Other Banks”))
VAR SavingsDeposits = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Savings Deposits”))
VAR EP = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Electronic Purse”))
VAR ShortDeposits = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Short Deposits”))
VAR DemandDom = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Demand Domiciliary”))
VAR TermDeposit = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Term Deposits”))
VAR FixedDom = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Fixed Domiciliary”))
VAR ConsumerFinance = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Demand Deposits”))
VAR RetailFinance = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Retail Finance”))
VAR Overdrafts = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Overdrafts”))
VAR TenoredLoans = CALCULATE([LY], FILTER(BS_Updated,BS_Updated[CAPTION]=“Tenored Loans”))

Return
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Demand Deposits”,DemandDeposits,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Collections_Remittances”,Collections,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Balances with Other Banks”,Bal,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Savings Deposits”,SavingsDeposits,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Electronic Purse”,EP,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Term Deposits”,TermDeposit,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Short Deposits”,ShortDeposits,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Demand Domiciliary”,DemandDom,
IF(SELECTEDVALUE(BS_Updated[CAPTION])=“Fixed Domiciliary”,FixedDom,
20 )))))))))

Where it False it should Output 20.

Then I went ahead to define the master measure( LY_Bal’000) which brings in all values (both subtotals and others.

LY Bal ('000) =

VAR Current_Item = SELECTEDVALUE(Template[Balance Sheet Normalized])

RETURN

SWITCH(TRUE(),

Current_Item = “Current”, DIVIDE([Current],1000,0),

Current_Item = “Savings”, DIVIDE([Savings],1000,0),

Current_Item = “LCY CASA”, DIVIDE([LCY CASA],1000,0),

Current_Item = “LCY FIXED DEPOSIT”, DIVIDE([LCY FIXED DEPOSIT],1000,0),

Current_Item = “Time Deposits”, DIVIDE([Time Deposit],1000,0),

Current_Item = “LCY DEPOSIT”, DIVIDE([LY_LCY DEPOSIT],1000,0),

Current_Item = “FCY DEPOSIT”, DIVIDE([LY_FCY DEPOSIT],1000,0),

Current_Item = “Total Customer Deposits”, DIVIDE([Total Customer Deposits],1000,0),

CALCULATE(‘Last Year Measures’[Last Year Bal], FILTER(‘Template’,Template[Balance Sheet (Average) For ]= Current_Item)

))

The Problem now is asides from sub totals, other values return 20.
Output:

image
image.png613×1089 120 KB

Please I need to know what is wrong, there is no synthax error in the dax formulas

2

Hello Phaniey

In the first measure(Last Year BAL) the coordinates of cells in the table BS_Updated are used as filter criteria, then the calculation of the cell is carried out. With the other measure LY Bal ('000) you iterate through the table Template so the coordinates that were active in the first measure are not active. I think the part of your measure as shown below can not be calculated because of the failing ‘filter’ machine.

CALCULATE(‘Last Year Measures’[Last Year Bal], FILTER(‘Template’,Template[Balance Sheet (Average) For ]= Current_Item

Gr Daniel

3

Hi Daniel,

Thanks for the response I guess you are right. But if that filter machine is wrong then what can be used there.
I had also tried the first measure (last year bal) without the filter and the I got the same result. Please could you suggest what I could use instead of that filter thanks.

4

Hi Phaniey,

Could you provide a pbix file with sample data?

5

Hi Daniel,

https://drive.google.com/file/d/1kBovRvw7Bfb-2MR9yCq5D1m_pCdri8EU/view?usp=sharing

This is the link to the file.

6

Hi Phaniey,

You can try to combine it to one measure like here below. In this way the filter coordinates all take place in the Template table and the calculation can be executed. Maybe you’ll have to check spaces etc. in the values to include all the right stuff.

LY D =
VAR WholesaleFinFac = [LY_Wholesale Finance Facility]
VAR DemandDeposits = [LY_Demand_Deposit]
VAR Collections = [LY_Collections/Remittances]
VAR Bal = [LY_Bal_OtherBanks]
VAR SavingsDeposits = [LY_Savings Deposits]
VAR EP = [LY_Electronic Purse]
VAR ShortDeposits = [LY_Short Deposits]
VAR DemandDom = [LY_Demand Domiciliary]
VAR TermDeposit = [LY_Term Deposits]
VAR FixedDom = [LY_Fixed Domiciliary]
VAR ConsumerFinance = [LY_Consumer Finance]
VAR RetailFinance = [LY_Retail Finance]
VAR Overdrafts = [LY_Overdrafts]
VAR TenoredLoans = [LY_Tenored Loans]
RETURN
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Wholesale Finance Facility”;
WholesaleFinFac;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Demand Deposits”;
DemandDeposits;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Collections/Remittances”;
Collections;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Balances with Other Banks”;
Bal;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Savings Deposits”;
SavingsDeposits;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Electronic Purse”;
EP;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Term Deposits”;
TermDeposit;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Short Deposits”;
ShortDeposits;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Demand Domiciliary”;
DemandDom;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Fixed Domiciliary”;
FixedDom;
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Current”;
DIVIDE ( [Current]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Savings”;
DIVIDE ( [Savings]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “LCY CASA”;
DIVIDE ( [LCY CASA]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “LCY FIXED DEPOSIT”;
DIVIDE ( [LCY FIXED DEPOSIT]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Time Deposits”;
DIVIDE ( [Time Deposit]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “LCY DEPOSIT”;
DIVIDE ( [LY_LCY DEPOSIT]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “FCY DEPOSIT”;
DIVIDE ( [LY_FCY DEPOSIT]; 1000; 0 );
IF (
SELECTEDVALUE ( Template[Balance Sheet Normalized] ) = “Total Customer Deposits”;
DIVIDE ( [Total Customer Deposits]; 1000; 0 );
20
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

Pic%20X

7

Hi Daniel,

Thanks. I did something very similar and it worked. Thanks for the help.

8

You’re welcome Phaniey