Forum Discussion
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 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:
Please help, how i can modify my code? Thank you
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)
16 Replies
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 Net8/7/2024 12:00:00 AM 97643520 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 AM 103015680 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 Date Net 8/7/2024 -96626400 8/7/2024 -1017120 8/7/2024 97643520 8/8/2024 -97643520 9/30/2024 103015680 Any idea, how i can modify my code? TIA
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.
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!Apologies, for my lapse in understanding. The table I refferd to earlier is not a physical table.
My XIRR calculation Measure is :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.
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.
you would still need to provide more realistic sample data.
