Forum Discussion
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 : ...
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]))RETURNXIRR(_Table,[Net],[GL Date],-0.1,0)
Sorry, I've been trying to upload the data file, but somehow I can't see any upload button when i click reply. Any idea how i can best share the data file here?
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]))RETURNXIRR(_Table,[Net],[GL Date],-0.1,0)Min date = min(GL Date)
Max date = 9/30/2024
Trans Type filters:Trans Types For Cash Flows(Net): credits only - debits only For 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 Date Deal Name Trans Type Debits only Credits only23/2/2023 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 28000 0 23/2/2023 C Purchase - FOF - Call for Investments 3872000 0 23/2/2023 C Purchase - FOF - Call for Management Fees (aff Cmt) 100000 0 23/2/2023 C Legacy Control a/c 0 4000000 20/7/2023 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 3500 0 20/7/2023 C Purchase - FOF - Call for Investments 3255000 0 20/7/2023 C Purchase - FOF - Call for Management Fees (aff Cmt) 241500 0 20/7/2023 C Legacy Control a/c 0 3500000 26/10/2023 D Purchase - FOF - Call for Investments 2325000 0 26/10/2023 D Purchase - FOF - Call for Management Fees (aff Cmt) 175000 0 26/10/2023 D Legacy Control a/c 0 2500000 20/11/2023 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 4000 0 20/11/2023 C Purchase - FOF - Call for Investments 3756000 0 20/11/2023 C Purchase - FOF - Call for Management Fees (aff Cmt) 240000 0 20/11/2023 C Legacy Control a/c 0 4000000 1/1/2024 D Memo: Geographic Designation 0 1000 1/1/2024 D Memo: Industry Sector 0 250 1/1/2024 D Memo: Industry Sector 0 250 1/1/2024 D Memo: Industry Sector 0 250 1/1/2024 D Memo: Industry Sector 0 250 1/1/2024 C Memo: Geographic Designation 0 1000 1/1/2024 C Memo: Industry Sector 0 200 1/1/2024 C Memo: Industry Sector 0 600 1/1/2024 C Memo: Industry Sector 0 200 2/1/2024 C Unrealized appreciation/depreciation 0 156930 2/1/2024 C Gain: unrealized 156930 0 27/2/2024 C Cash disbursed 0 4000000 27/2/2024 C Investments Payable 4000000 0 28/2/2024 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 4000 0 28/2/2024 C Purchase - FOF - Call for Investments 3756000 0 28/2/2024 C Purchase - FOF - Call for Management Fees (aff Cmt) 240000 0 28/2/2024 C Investments Payable 0 4000000 31/5/2024 C Valuation final (value) 0 14858276 31/5/2024 C Unrealized appreciation/depreciation 0 484794 31/5/2024 C Gain/loss: unrealized 484794 0 14/6/2024 C Cash disbursed 0 8500000 14/6/2024 C Investments Payable 8500000 0 17/6/2024 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 8500 0 17/6/2024 C Purchase - FOF - Call for Investments 8248400 0 17/6/2024 C Purchase - FOF - Call for Management Fees (aff Cmt) 243100 0 17/6/2024 C Investments Payable 0 8500000 25/6/2024 D Unrealized appreciation/depreciation 0 449835 25/6/2024 D Gain/loss: unrealized 449835 0 10/7/2024 D Cash disbursed 0 3144654.09 10/7/2024 D Purchase - FOF - Call for Fund Expenses (aff Cmt) 106918.24 0 10/7/2024 D Purchase - FOF - Call for Investments 3037735.85 0 10/7/2024 D Investments Payable 0 3144654.09 10/7/2024 D Investments Payable 3144654.09 0 31/8/2024 D Valuation final (value) 0 4934977.09 31/8/2024 D Unrealized appreciation/depreciation 0 259842 31/8/2024 D Gain: unrealized 259842 0 31/8/2024 C Valuation final (value) 0 24541822 31/8/2024 C Unrealized appreciation/depreciation 1183546 0 31/8/2024 C Gain: unrealized 0 1183546 24/9/2024 C Cash disbursed 0 3500000 24/9/2024 C Purchase - FOF - Call for Fund Expenses (aff Cmt) 3500 0 24/9/2024 C Purchase - FOF - Call for Investments 3253250 0 24/9/2024 C Purchase - FOF - Call for Management Fees (aff Cmt) 243250 0 24/9/2024 C Investments Payable 0 3500000 24/9/2024 C Investments Payable 3500000 0 
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