Translating Sheet Formulas into DAX

[Yrstruly2021]

1. Please assist,  @Jihwan_Kim, @123abc.  I have converted formulas from a spreadsheet(https://docs.google.com/spreadsheets/d/1ubo9TAz2zzCpKbfO5SFBMjFLgjFtUxhY/edit#gid=2078998761) into DAX. I just need a person to please go over the steps and assure me that my conversions/calculations are correct.

2. The Dax Calculation is for " Cash flow imbalance".

2.1 Click in column K and you will see the formula. You need to go to each cell that is referenced to get the complete calculation and so on.

 My Dax for the above row:  

Cash flow imbalance = 
[Cash flow from ops - management]
- [Cash flow from ops - statutory]
+ CALCULATE(
    SUM('ZTBR'[Amount in USD]),
    'ZTBR'[Roll_Up_Function] = "B allocations not in mgt cash flow"
)

2.2 Dax calculations are under Metric calculations

PBIX: https://drive.google.com/file/d/1boh0etrQH-_zo6Cknob07tsO1jgmPRmY/view?usp=sharing 

The Main code 

Totals by Account Description & Code5 = 
VAR SelectedCashFlowDescription = SELECTEDVALUE('CashflowDiscriptions'[Description], "None")
VAR _TotalNetDept = CALCULATE([Net debt increase/decrease], ALL(CashflowDiscriptions))
VAR _TotalNetTaxPaid = CALCULATE([Net Tax Paid], ALL(CashflowDiscriptions))
VAR _TotalIntercoCashFlows = CALCULATE([IntercoCashFlows], ALL(CashflowDiscriptions))
VAR _TotalOtherCashFlowItems = CALCULATE([Other cash flow items], ALL(CashflowDiscriptions))
VAR _TotalInterestPaid = CALCULATE([Interest paid], ALL(CashflowDiscriptions))
VAR _TotalInterestExpenseNotImpactingCashFlow = CALCULATE([Interest expense not impacting cash flow], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsStatutory = CALCULATE([Cash flow from ops - statutory], ALL(CashflowDiscriptions))
VAR _TotalOtherCashFlowfromTradingAdjustments = CALCULATE([Other cash flow from trading adjustments], ALL(CashflowDiscriptions))
VAR _TotalDisposalsImpairmentofFixedAssets = CALCULATE([Disposals & impairment of fixed assets], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsExclProvisions = CALCULATE([Working capital mvts excl. provisions], ALL(CashflowDiscriptions))
VAR _TotalOtherPPEAcquired = CALCULATE([Other PP&E acquired], ALL(CashflowDiscriptions))
VAR _TotalPurchasesOfPPE = CALCULATE([Purchases of PP&E], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsInclProvisions = CALCULATE([WorkingCapitalMvtsInclProvisions], ALL(CashflowDiscriptions))
VAR _TotalProceedsOtherPPEDisposals = CALCULATE([Proceeds other PP&E disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsPoolingDisposals = CALCULATE([Proceeds pooling disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsFromDisposals = CALCULATE([Proceeds from disposals], ALL(CashflowDiscriptions))
VAR _TotalPoolingEquipmentAcquired = CALCULATE([Pooling equipment acquired], ALL(CashflowDiscriptions))
VAR _TotalDisposalProceedsCashFlowAdjustments = CALCULATE([Disposal proceeds cash flow adjustments], ALL(CashflowDiscriptions))
VAR _TotalCapitalExpenditure = CALCULATE([Capital expenditure], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalBudgetFlex = CALCULATE([WorkingCapitalMvtsInclProvisions], ALL(CashflowDiscriptions))
VAR _TotalCashFlowfromTrading = CALCULATE([CashFlowFromTrading], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsManagement = CALCULATE([Cash flow from ops - management], ALL(CashflowDiscriptions))
VAR _TotalCashFlowImbalance = CALCULATE([Cash flow imbalance], ALL(CashflowDiscriptions))
VAR _LeasesPrincipalRepaid = CALCULATE(SUM(Bracs[Metric Value ($)]), Bracs[Metric] = "Leases principal repaid")

RETURN
    SWITCH(
        TRUE(),
        SelectedCashFlowDescription = "Interco capital returned", 678,
        SelectedCashFlowDescription = "Net debt increase/(decrease)", _TotalNetDept,
        SelectedCashFlowDescription = "Cash flow imbalance", _TotalCashFlowImbalance,
        SelectedCashFlowDescription = "Cash flow from ops - management",_TotalCashFlowFromOpsManagement,
        SelectedCashFlowDescription = "Working capital mvts incl. provisions", _TotalWorkingCapitalBudgetFlex,
        SelectedCashFlowDescription = "Proceeds from disposals ", _TotalProceedsFromDisposals,
        SelectedCashFlowDescription = "Proceeds other PP&E disposals", _TotalProceedsOtherPPEDisposals,
        SelectedCashFlowDescription = "Proceeds pooling disposals", _TotalProceedsPoolingDisposals,
        SelectedCashFlowDescription = "Capital expenditure", _TotalCapitalExpenditure,
        SelectedCashFlowDescription = "Net tax paid", _TotalNetTaxPaid,
        SelectedCashFlowDescription = "IntercoCashFlows", _TotalIntercoCashFlows,
        SelectedCashFlowDescription = "Leases principal repaid", _LeasesPrincipalRepaid,
        SelectedCashFlowDescription = "OtherCashFlowItems", _TotalOtherCashFlowItems,
        SelectedCashFlowDescription = "Interestpaid", _TotalInterestPaid,
        SelectedCashFlowDescription = "Interest expense not impacting cash flow", _TotalInterestExpenseNotImpactingCashFlow,
        SelectedCashFlowDescription = "CashFlowFromOps - Statutory", _TotalCashFlowFromOpsStatutory,
        SelectedCashFlowDescription = "Other cash flow from trading adjustments", _TotalOtherCashFlowfromTradingAdjustments,
        SelectedCashFlowDescription = "Disposals & impairment of fixed assets", _TotalDisposalsImpairmentofFixedAssets,
        SelectedCashFlowDescription = "Working capital mvts excl. provisions", _TotalWorkingCapitalMvtsExclProvisions,
        SelectedCashFlowDescription = "Working capital mvts incl. provisions", _TotalWorkingCapitalMvtsInclProvisions,
        SelectedCashFlowDescription = "Cash flow from trading", _TotalCashFlowfromTrading,
        SelectedCashFlowDescription = "Pooling equipment acquired", _TotalPoolingEquipmentAcquired,
        SelectedCashFlowDescription = "Other PP&E acquired", _TotalOtherPPEAcquired,
        SelectedCashFlowDescription = "Purchases of PP&E", _TotalPurchasesOfPPE,
        SelectedCashFlowDescription = "Working capital mvts incl. provisions", _TotalWorkingCapitalBudgetFlex,
        
        // Default calculation if no specific match is found
        CALCULATE(
            SUM('ZTBR'[Amount in USD]),
            'ZTBR'[Roll_Up_Function] IN {
            "Cash share issues by B Limited",
        "FX on foreign currency denominated debt",
        "Cost sharing payments to B Limited",
        "Cash paid for share buyback",
        "Equity raised cash flow adjustment",
        "Cash flow from ops - management",
        "Cash flow from trading",
        "Change in capex creditors",
        "Profit before B allocations Total",
        "Depreciation and amortisation",
        "IPEP expense",
        "Disposals & impairment of fixed assets",
        "Profit on disposal of pooling equipment",
        "Scrapped pooling equipment",
        "Impairment or valuation adjustment of pooling equipment",
        "Disposals or valuation adjustments of other fixed assets",
        "Other cash flow from trading adjustments",
        "Other current debtors",
        "Share-based payments expense",
        "Working capital mvts excl. provisions",
        "Debtor movements",
        "Creditor movements",
        "Inventory movements",
        "Prepayment movements",
        "Change in loss compensation balances",
        "Provision movements",
        "FX on foreign currency denominated debt Total",
        "B allocations not in mgt cash flow",
        "Interco interest and guarantee fees",
        "Interco cash flows",
        "Interco royalties",
        "Interco cash flow adjustments",
        "Internal restructuring",
        "Interco dividends Total",
        "Change in interco balances",
        "Change in interco recharge clearing",
        "FX on interco debt",
        "Interco capital returned",
        "Interco cash flow adjustments",
        "Interest expense Total",
        "Interest revenue",
        "Interest paid",
        "Interest received",
        "Cash flow from ops - statutory adjustment",
        "Lease interest",
        "Change in loss compensation balances",
        "Statutory reallocations",
        "Change in deferred fixed asset proceeds",
        "Proceeds disposals of intangible assets",
        "Proceeds software intangibles disposals",
        "WDV software intangibles disposals",
        "Profit software intangibles disposals",
        "Proceeds other intangibles disposals",
        "WDV other intangibles disposals",
        "Profit other intangibles disposals",
        "Proceeds deferred expenditure disposals",
        "WDV deferred expenditure disposals",
        "Profit deferred expenditure disposals",
        "Proceeds investment disposals",
        "WDV investments disposals",
        "Profit investments disposals",
        "Disposal proceeds cash flow adjustments",
        "B allocations not in mgt cash flow",
        "Cash flow from ops - statutory adjustment"
            }
        ) + CALCULATE(
            SUM(Bracs[Metric Value ($)]),
            Bracs[Metric] IN {
            "Entity acquisitions and disposals",
"Entity acquisitions",
"Total purchase price of entities acquired",
"Purchase price of entities acquired",
"Cash price of entities acquired",
"Non-cash consideration in purchase price",
"Costs directly incurred on entity acquisitions",
"Deferred settlement of entity acquisitions",
"Deferred settlements now paid",
"Non-cash consideration for entity acquisitions",
"Net cash held by entity at date of acquisition",
"Entity acquisition cash flow adjustments",
"Proceeds from entity disposals",
"Net price of entities disposed",
"Agreed sale price of entities disposed",
"Cash price of entities disposed",
"Non-cash consideration in agreed sale price",
"Costs directly incurred on entity disposals",
"Provisions raised on entity disposals",
"Net change disposal provisions and accruals",
"Deferred settlement of entity disposals",
"Deferred settlements now received",
"Non-cash consideration for entity disposals",
"Net cash held by entity at date of disposal",
"Entity disposal cash flow tax adjustments",
"Entity disposal cash flow other adjustments",
"Pension plan adjustment",
                "Pension plan adjustment",
                "Working capital - budget flex",
                "Pooling equipment additions",
                "Pooling equipment replacements",
                "Pooling equipment internal transfers",
                "Other PP&E additions",
                "Other PP&E replacements",
                "Other PP&E internal transfers",
                "Joint venture loans",
                "WDV pooling equip. disposals & write-offs",
                "Gain pooling equip. disposals & write-offs",
                "WDV other PP&E disposals",
                "Profit other PP&E disposals",
                "Discount unwind on long term provisions",
                "Tax paid",
                "Joint venture loans",
                "Tax refunded",
                "Working capital - budget flex",
                "Fiscal unity tax transfers",
                "Other cash flow items",
                "FX adjustments to cash flow",
                "Change in cash net of overdraft"
            }
        )
    )

[Anonymous]

Hi, @Yrstruly2021 

I would like to ask if an error occurred during operation?  Do you need to check the calculation logic?Here are some of the results I checked：

Cash flow from ops – management maybe wrong:

Working capital mvts excl. provisions maybe wrong:

The following two columns did not find the corresponding columns:

I have checked part of it, the specific results are as follows:

Not found means that the method was not found in the PBIX file, No means that the condition is missing, or the usage is inconsistent with the one in Excel.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yrstruly2021]

Thank you for the reply. The missing "columns" refer to the numeric values i removed due to privacy. Those are the only two columns that are missing, so my calculations shows errors when those columns are removed. No, my PBIX file ran without any errors. I just need to make sue that the first metric as mentioned are calcullated correctly. Why do you say these two(Cash flow from ops – management maybe wrong, Working capital mvts excl. provisions ) might be wrong?