Forum Discussion
Financial Statements Dax Measures
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])
7 Replies
- Joe_Barry
Solution Sage
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
"Reserves"its there actually i just took random screen shot just for understaning
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]
- 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])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
- DataNinja777
Super User
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,
