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)
yes, you can add a filter to the above code that excludes days where [n] is zero.
Good solution, but some doesnt work. The change FIlter/Sort needs to be applied at the table level.
While trying something,
Any idea why the Table is not being sorted by the [Net] values?
Output is still: