Forum Discussion
Sorting the temporary table
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)
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.
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?
yes, you can add a filter to the above code that excludes days where [n] is zero.
