Financial Statements Dax Measures

[mohsinmasood]

I have financial statement format for that i am using measures with switch to display the value but one of the measures is not giving me right value when placed with switch function alone that measure gives me correct amount in card visual.

below measure gives me correct amount when i used in card visual

Measure Formula

Reserves = CALCULATE(SUM('Financial Data'[Accounted Net]),ALL('Financial Data'[Accounted Net]),'Financial Data'[Group dUPLICATE acc type]="Owners' equity")+[Net Profit for Equity]

problem happens when i placed the same measure in Switch

Switch Formula

   SWITCH(SELECTEDVALUE('Financial Data'[Group]),"Accruals",[Accruals],"Reserves",[Reserves],"Accrued revenue",[Accrued Revenue],"Cash and bank",[Cash and bank],"Caregiver Loans Recoverables",[Caregiver Loans Recoverables],"Deposits & advances",[Deposits & advances],"Employee payables",[Employee payables],"EOSB",[EOSB],"Intangible Assets",[Intangible Assets],"Inventory",[Inventory],"Lease Liability- C",[Lease Liability- C],"Other payables",[Other payables],"Other receivables",[Other receivables],"Prepaid expenses",[Prepaid expenses],"Property, Plant and Equipment",[Propert plant and equipment],"ROU assets",[ROU assets],"Tax payable",[Tax Payable],"Trade payables",[Trade Payables],"Trade receivable",[Trade receivable],"Additional shareholder contribution",[Additional shareholder contribution],"Share capital",[Share capital])

[DataNinja777]

Hi @mohsinmasood ,

First of all, may I ask you if your company uses ERP?  I would have thought that standard ERP system can generate such standard financial statements, like BS and PL.  Of course ERP generated financial statements tend to be just basic ones, and Power BI can give much more flexibly regarding the visualizations.  Also getting ERP helpdesk (like SAP) to make modifications to suit your requirements can be super slow and can take months and years to get things done while comparable things can be done by Power BI in a fraction of the time if we have the access. 😂

As you know, while the BS is the accumulation of all the transactions for the BS GL since the inception, PL is just for transactions for the year, and the reserve is accumulating the PL GL amounts in one GL since the inception up to the end of the previous fiscal year.  With this logic in mind, it is definitely doable to generate financial statements from the raw transaction data using Power BI.  But companies I've seen have ERP in place, so I never had to do this from scratch from the transaction data 🙂.  

My observation on your switch formula is that, instead of writing each account grouping in the formula, mapping all these accounts as "BS" or "PL" in the chart of accounts mapping will make things simpler, and prevents you from manually writing additional GL accounts in the formula if a new GL is added in your CoA.  Mapping is the key to the automation of the financial reporting, and I would create a mapping like below, and write a formula to say that if it is BS account, accumulate the transaction since the inception, while it is PL, just accumulate since the beginning of the year, and for the previous year PL transactions transfer to the retained earnings equity GL which is mapped to Equity line.  

BS will have Assets, Liabilities, and Equity as FS category, while PL will have Revenue and Expense as FS category.  FYI, I assumed that the Amortization and Depreciation shown in the table below are from the PL side instead of the BS side because they do not say "Accumulated Depreciation" or "Accumulated Amortization", etc. 

Group	BS/PL	FS Category
Accruals	BS	Liability
Accrued revenue	BS	Liability
Additional shareholder contribution	BS	Equity
Amortization on intangibles	PL	Expense
Bonus and incentives (I)	PL	Expense
Caregiver Loans Recoverables	BS	Asset
Cash and bank	BS	Asset
CBD/ Visiting physician fee	PL	Expense
Control account	BS	Asset
Deposits and advances	BS	Asset
Depreciation on PPE	PL	Expense
Depreciation on ROU assets	PL	Expense
Employee payables	BS	Liability
EOSB	PL	Expense
Facilities cost (D)	PL	Expense
Facilities cost (I)	PL	Expense

Best regards,

[Joe_Barry]

Please try  the below

But what are trying achive here with this part?  +[Net Profit for Equity]

Reserves = 
CALCULATE(
   SUM('Financial Data'[Accounted Net]),
     KEEPFILTERS('Financial Data'[Group dUPLICATE acc type] IN {"Owners", "equity"),
ALL('Financial Data'[Accounted Net])

[mohsinmasood]

actually i am trying to build Balance sheet where we have to add net income for the year to owners equity

[Joe_Barry]

Hi @mohsinmasood 

So you need to add [Net Profit for Equity] to the answer of the first part?

Reserves = 
VAR _OwnEq = 
CALCULATE(
   SUM('Financial Data'[Accounted Net]),
     KEEPFILTERS('Financial Data'[Group dUPLICATE acc type] IN {"Owners", "equity"),
ALL('Financial Data'[Accounted Net])

RETURN
_OwnEq + [Net Profit for Equity]

Presuming [Net Profit for Equity] is a measure

[Joe_Barry]

Hi @mohsinmasood 

Please use the code formatting, this makes it easier to read measures.

Looking at the screenshot of the table, Reserves doesn't appear in the Group column. You need to add it there for it to appear.

Hope this helps

Joe

[mohsinmasood]

"Reserves"its there actually i just took random screen shot just for understaning 

[mohsinmasood]

It gives me value of this calculation

Reserves = CALCULATE(SUM('Financial Data'[Accounted Net]),ALL('Financial Data'[Accounted Net])

but i want below calculation value

Reserves = CALCULATE(SUM('Financial Data'[Accounted Net]),ALL('Financial Data'[Accounted Net]),'Financial Data'[Group dUPLICATE acc type]="Owners' equity")+[Net Profit for Equity]