Forum Discussion

visheshvats1's avatar
visheshvats1
Helper I
1 year ago
Solved

Sorting the temporary table

Hello, Trying to calculate XIRR, but the result varies even on the sort order of values. My DAX to calculate a table, and use it to calculate XIRR is as follows:      Output is :    ...
Need Help
  • visheshvats1's avatar
    visheshvats1
    1 year ago

    I managed to sort the dynamic table within my measure. I needed to create a caculated column which has 0 if the sum of Net is zero for a given date for any deal name. Create another column to be 1 if value in this new column is <0 else 2.
    Now split into 3 parts and then combine using union function. 

    My measure: 

    VAR _Table = UNION(
        SELECTCOLUMNS(
            FILTER(
                'General Ledger',
                'General Ledger'[GL Date] >= [MIN Date] &&
                'General Ledger'[GL Date] <= [MAX Date] &&
                'General Ledger'[Trans Type] IN Trantype&&
                'General Ledger'[Column]<>0&&
                'General Ledger'[Index]=1
        ),
            "GL Date", 'General Ledger'[GL Date],
            "Net", 'General Ledger'[Column]    
        ),
        SELECTCOLUMNS(
            FILTER(
                'General Ledger',
                'General Ledger'[GL Date] >= [MIN Date] &&
                'General Ledger'[GL Date] <= [MAX Date] &&
                'General Ledger'[Trans Type] IN Trantype&&
                'General Ledger'[Column]<>0&&
                'General Ledger'[Index]=2
        ),
            "GL Date", 'General Ledger'[GL Date],
            "Net", 'General Ledger'[Column]  
        ),
        ROW(
            "GL Date", [MAX Date],  
            "Net", [Net sheet 1]
           
        ))
    RETURN
    XIRR(_Table,[Net],[GL Date],-0.1,0)



visheshvats1's avatar
visheshvats1
Helper I
1 year ago

Min date = min(GL Date)

Max date = 9/30/2024

Trans Type filters: 

 

Trans Types
For Cash Flows(Net): credits only - debits onlyFor NAV (Net Sheet 1): debits only - credits only
Accrued portfolio dividend receivable,Accumulated Depreciation,
Accrued Rental Income,Acquisition Adjustment,
Bond - interest receivable,Adjustment- do not post,
Capitalized fee/expense,Adjustment- do not post (Recallable),
Distribution - Current income - (Recallable),Bond - interest receivable,
Distribution - Current income (Non-Recallable),Bonus Shares,
Dividend Income - Recallable,Capitalized fee/expense,
Gain: realized f/x, long term - FOF,Distribution - Current income - (Recallable),
Gain: realized, long term - FOF,Distribution - Current income (Non-Recallable),
Income: dividend - cash,Dividend Income - Recallable,
Income: dividend - pik,Investment Property,
Income: dividend accrual,Loan - Interest Receivable,
Income: fees, investment,Loan Receivable,
Income: interest - cash, investment,Purchase,
Income: interest - pik,Purchase - FOF - Call for Fund Expenses (aff Cmt),
Income: interest accrual,Purchase - FOF - Call for Fund Expenses (Not aff Cmt),
Income: other, investment,Purchase - FOF - Call for Investments,
Investment Property,Purchase - FOF - Call for Management Fees (aff Cmt),
Investments Payable,Purchase - FOF - Call for Management Fees (Not aff Cmt),
Investments Receivable,Purchase - FOF - Call for Operational Expenses (aff Cmt),
Loan - Interest Receivable,Purchase - FOF - Call for Operational Expenses (Not aff Cmt),
Loan Receivable,Purchase: PIK,
Loss: realized f/x, long term,Purchase: security conversion,
Loss: realized f/x, long term,Realized Gains/Losses - FOF (Non Recallable),
Loss: realized, long term,Realized Gains/Losses - FOF (Recallable),
Other receivable,Receivable: interest,
Purchase,Receivable: interest, accrued,
Purchase - FOF - Call for Fund Expenses (aff Cmt),Return of capital,
Purchase - FOF - Call for Fund Expenses (Not aff Cmt),Return of Capital - FOF (Non Recallable),
Purchase - FOF - Call for Investments,Return of Capital - FOF (Recallable),
Purchase - FOF - Call for Management Fees (aff Cmt),Return of capital: stock distribution to partners,
Purchase - FOF - Call for Management Fees (Not aff Cmt),Return of Excess Contributions,
Purchase - FOF - Call for Operational Expenses (aff Cmt),Sell,
Purchase - FOF - Call for Operational Expenses (Not aff Cmt),Sell- do not use,
Purchase: PIK,Unrealized appreciation/depreciation,
Purchase: security conversion,Unrealized depreciation,
Realized Gains/Losses - FOF (Non Recallable),Unrealized f/x appreciation/depreciation
Realized Gains/Losses - FOF (Recallable), 
Receivable: interest, 
Receivable: interest, accrued, 
Rental income, 
Return of capital, 
Return of Capital - FOF (Non Recallable), 
Return of Capital - FOF (Recallable), 
Return of capital: stock distribution to partners, 
Return of Excess Contributions, 
Sell, 
Withholding tax on Dividend 

 

Data:

GL DateDeal NameTrans TypeDebits only
Credits only
23/2/2023CPurchase - FOF - Call for Fund Expenses (aff Cmt)280000
23/2/2023CPurchase - FOF - Call for Investments38720000
23/2/2023CPurchase - FOF - Call for Management Fees (aff Cmt)1000000
23/2/2023CLegacy Control a/c04000000
20/7/2023CPurchase - FOF - Call for Fund Expenses (aff Cmt)35000
20/7/2023CPurchase - FOF - Call for Investments32550000
20/7/2023CPurchase - FOF - Call for Management Fees (aff Cmt)2415000
20/7/2023CLegacy Control a/c03500000
26/10/2023DPurchase - FOF - Call for Investments23250000
26/10/2023DPurchase - FOF - Call for Management Fees (aff Cmt)1750000
26/10/2023DLegacy Control a/c02500000
20/11/2023CPurchase - FOF - Call for Fund Expenses (aff Cmt)40000
20/11/2023CPurchase - FOF - Call for Investments37560000
20/11/2023CPurchase - FOF - Call for Management Fees (aff Cmt)2400000
20/11/2023CLegacy Control a/c04000000
1/1/2024DMemo: Geographic Designation01000
1/1/2024DMemo: Industry Sector0250
1/1/2024DMemo: Industry Sector0250
1/1/2024DMemo: Industry Sector0250
1/1/2024DMemo: Industry Sector0250
1/1/2024CMemo: Geographic Designation01000
1/1/2024CMemo: Industry Sector0200
1/1/2024CMemo: Industry Sector0600
1/1/2024CMemo: Industry Sector0200
2/1/2024CUnrealized appreciation/depreciation0156930
2/1/2024CGain: unrealized1569300
27/2/2024CCash disbursed04000000
27/2/2024CInvestments Payable40000000
28/2/2024CPurchase - FOF - Call for Fund Expenses (aff Cmt)40000
28/2/2024CPurchase - FOF - Call for Investments37560000
28/2/2024CPurchase - FOF - Call for Management Fees (aff Cmt)2400000
28/2/2024CInvestments Payable04000000
31/5/2024CValuation final (value)014858276
31/5/2024CUnrealized appreciation/depreciation0484794
31/5/2024CGain/loss: unrealized4847940
14/6/2024CCash disbursed08500000
14/6/2024CInvestments Payable85000000
17/6/2024CPurchase - FOF - Call for Fund Expenses (aff Cmt)85000
17/6/2024CPurchase - FOF - Call for Investments82484000
17/6/2024CPurchase - FOF - Call for Management Fees (aff Cmt)2431000
17/6/2024CInvestments Payable08500000
25/6/2024DUnrealized appreciation/depreciation0449835
25/6/2024DGain/loss: unrealized4498350
10/7/2024DCash disbursed03144654.09
10/7/2024DPurchase - FOF - Call for Fund Expenses (aff Cmt)106918.240
10/7/2024DPurchase - FOF - Call for Investments3037735.850
10/7/2024DInvestments Payable03144654.09
10/7/2024DInvestments Payable3144654.090
31/8/2024DValuation final (value)04934977.09
31/8/2024DUnrealized appreciation/depreciation0259842
31/8/2024DGain: unrealized2598420
31/8/2024CValuation final (value)024541822
31/8/2024CUnrealized appreciation/depreciation11835460
31/8/2024CGain: unrealized01183546
24/9/2024CCash disbursed03500000
24/9/2024CPurchase - FOF - Call for Fund Expenses (aff Cmt)35000
24/9/2024CPurchase - FOF - Call for Investments32532500
24/9/2024CPurchase - FOF - Call for Management Fees (aff Cmt)2432500
24/9/2024CInvestments Payable03500000
24/9/2024CInvestments Payable35000000
visheshvats1's avatar
visheshvats1
Helper I
1 year ago

I managed to sort the dynamic table within my measure. I needed to create a caculated column which has 0 if the sum of Net is zero for a given date for any deal name. Create another column to be 1 if value in this new column is <0 else 2.
Now split into 3 parts and then combine using union function. 

My measure: 

VAR _Table = UNION(
    SELECTCOLUMNS(
        FILTER(
            'General Ledger',
            'General Ledger'[GL Date] >= [MIN Date] &&
            'General Ledger'[GL Date] <= [MAX Date] &&
            'General Ledger'[Trans Type] IN Trantype&&
            'General Ledger'[Column]<>0&&
            'General Ledger'[Index]=1
    ),
        "GL Date", 'General Ledger'[GL Date],
        "Net", 'General Ledger'[Column]    
    ),
    SELECTCOLUMNS(
        FILTER(
            'General Ledger',
            'General Ledger'[GL Date] >= [MIN Date] &&
            'General Ledger'[GL Date] <= [MAX Date] &&
            'General Ledger'[Trans Type] IN Trantype&&
            'General Ledger'[Column]<>0&&
            'General Ledger'[Index]=2
    ),
        "GL Date", 'General Ledger'[GL Date],
        "Net", 'General Ledger'[Column]  
    ),
    ROW(
        "GL Date", [MAX Date],  
        "Net", [Net sheet 1]
       
    ))
RETURN
XIRR(_Table,[Net],[GL Date],-0.1,0)