Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
visheshvats1
Frequent Visitor

Sorting the temporary table

‎11-11-2024 12:20 AM

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: 
 

visheshvats1_1-1731312927259.png

 

Output is : 

 
visheshvats1_2-1731312975407.png

I wish to order the table in a manner that the output is sorted where for a given date the negative(-) values come first.
Desired output: 

visheshvats1_3-1731313016668.png

Please help, how i can modify my code? Thank you

Labels:
Message 1 of 17
318 Views
0
1 ACCEPTED SOLUTION
visheshvats1
Frequent Visitor
In response to visheshvats1

‎11-14-2024 02:24 AM

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)



View solution in original post

Message 14 of 17
109 Views
16 REPLIES 16
visheshvats1
Frequent Visitor

‎11-10-2024 10:29 PM

Hello, My DAX to calculate a table is as follows: 

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
        ),
        "GL Date", 'General Ledger'[GL Date],
        "Net", 'General Ledger'[Credits only]-'General Ledger'[Debits only]
    ),
    ROW(
        "GL Date", [MAX Date],  
        "Net", [Net sheet 1]
    )
)
RETURN
__Table

Output is : 

GL Date                                Net
8/7/2024 12:00:00 AM97643520
8/7/2024 12:00:00 AM-1017120
8/7/2024 12:00:00 AM-96626400
8/8/2024 12:00:00 AM-97643520
9/30/2024 12:00:00 AM103015680

 

I wish to order the table in a manner that the output is sorted where for a given date the negative(-) values come first.
Desired output: 

GL DateNet
8/7/2024-96626400
8/7/2024-1017120
8/7/202497643520
8/8/2024-97643520
9/30/2024103015680

 

Any idea, how i can modify my code? TIA

Message 16 of 17
194 Views
0
rajendraongole1
Super User
Super User
In response to visheshvats1

‎11-11-2024 09:45 AM

Hi @visheshvats1 - DAX doesn’t have a direct way to sort a calculated table within the table expression itself. You can add a sorting column to your DAX code to specify the order of rows within the table.

 

Use below code:

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
),
"GL Date", 'General Ledger'[GL Date],
"Net", 'General Ledger'[Credits only] - 'General Ledger'[Debits only]
),
ROW(
"GL Date", [MAX Date],
"Net", [Net sheet 1]
)
)
VAR __SortedTable =
ADDCOLUMNS(
__Table,
"SortOrder",
RANKX(
__Table,
[GL Date] & IF([Net] < 0, "0", "1") & ABS([Net]),
,
ASC
)
)
RETURN
SELECTCOLUMNS(
TOPN(
COUNTROWS(__SortedTable),
__SortedTable,
[GL Date], ASC,
[SortOrder], ASC
),
"GL Date", [GL Date],
"Net", [Net]
)

 

 

Try the above code, you can get negative values first and remaining as expected in sort order. 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 17 of 17
189 Views
0
saud968
Solution Sage
Solution Sage

‎11-11-2024 12:43 AM

You can achieve this by creating a calculated column that assigns a sort order based on the sign of the values. Here’s an example of how you can modify your DAX code:

Create a calculated column to determine the sort order:
SortOrder = IF([Value] < 0, 0, 1)

Sort your table by the date and the new SortOrder column:
SortedTable =
ADDCOLUMNS(
YourTable,
"SortOrder", IF([Value] < 0, 0, 1)
)

Use the SortedTable to calculate XIRR:
XIRRResult =
XIRR(
ADDCOLUMNS(
YourTable,
"SortOrder", IF([Value] < 0, 0, 1)
),
[Value],
[Date]
)

This approach ensures that for each date, the negative values will appear first, followed by the positive values.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Message 2 of 17
287 Views
0
visheshvats1
Frequent Visitor
In response to saud968

‎11-11-2024 01:08 AM

Apologies, for my lapse in understanding. The table I refferd to earlier is not a physical table. 

My XIRR calculation Measure is : 

visheshvats1_0-1731315956790.png

 

As the mathematical function 'XIRR' needs the order of the values in an order where neagative values come before positive values at a given date. I wish to order the output of my temporary table within my measure. 

Hope my question is clearer now.

@lbendlin @Ritaf1983 

Message 3 of 17
282 Views
0
lbendlin
Super User
Super User
In response to visheshvats1

‎11-11-2024 05:51 AM 
As the mathematical function 'XIRR' needs the order of the values in an order where neagative values come before positive values at a given date.

That's not what the function definition says.  It does however require a grouping by date.

 

lbendlin_1-1731333046922.png

 

you would still need to provide more realistic sample data.

 

 

 

Message 4 of 17
234 Views
0
visheshvats1
Frequent Visitor
In response to lbendlin

‎11-11-2024 08:10 AM

Thanks for the response, as XIRR measures the annualized return on an investment portfolio, in accounting scenarios, the initial investment is always denoted as a -ve value, this can be checked on an excel sheet using the tabular data I shared. 

Another solution to this problem can be that if we could modify the code such that if the sum of [Net] for a given [GL Date] is 0 then those rows be excluded from the calculation altogether. Can you please suggest a way to achieve that?

Message 5 of 17
221 Views
0
lbendlin
Super User
Super User
In response to visheshvats1

‎11-11-2024 08:53 AM

yes, you can add a filter to the above code that excludes days where [n] is zero.

 

lbendlin_0-1731344314626.png

 

 

 

xirr zero.pbix
Message 6 of 17
208 Views
0
visheshvats1
Frequent Visitor
In response to lbendlin

‎11-11-2024 09:25 PM

Good solution, but some doesnt work. The change FIlter/Sort needs to be applied at the table level. 

 

While trying something, 

visheshvats1_0-1731389026347.png

 

Any idea why the Table is not being sorted by the [Net] values?
Output is still:

visheshvats1_1-1731389087883.png

 



Message 7 of 17
193 Views
0
lbendlin
Super User
Super User
In response to visheshvats1

‎11-11-2024 10:52 PM

There is no need for sorting the raw data. 

Message 8 of 17
185 Views
0
visheshvats1
Frequent Visitor
In response to lbendlin

‎11-12-2024 12:28 AM

Understood, and yes I could get the value using the expression share by you earlier. Thank you for that.
But this only works if i filter the table with a given 'deal name'. Not for each deal. 

Working Table dax: 

 

visheshvats1_1-1731399867702.png

 

But when i modify to accept any 'deal name':

visheshvats1_2-1731399947870.png

The subtotal is being repeated over and over: 

visheshvats1_3-1731400017404.png

Any idea on how to resolve this? Thank you.






Message 9 of 17
175 Views
0
lbendlin
Super User
Super User
In response to visheshvats1

‎11-12-2024 01:28 AM

Please provide sample data that fully covers your issue. In a usable form (not as screenshots).
Please show the expected outcome based on the sample data you provided.

Message 10 of 17
169 Views
0
visheshvats1
Frequent Visitor
In response to lbendlin

‎11-13-2024 04:52 AM

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?

Message 11 of 17
143 Views
0
visheshvats1
Frequent Visitor
In response to lbendlin

‎11-13-2024 10:56 AM

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
Message 13 of 17
129 Views
0
visheshvats1
Frequent Visitor
In response to visheshvats1

‎11-14-2024 02:24 AM

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)



Message 14 of 17
110 Views
v-linyulu-msft
Community Support
Community Support
In response to visheshvats1

‎11-14-2024 09:04 PM

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

Message 15 of 17
80 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

View All