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)



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,visheshvats1 
    We are delighted that you have found a solution and are willing to share it.

     

    Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

     

    Thank you for your valuable contribution to the community, and we wish you all the best in your work.

     

    Best Regards,

    Leroy Lu