Forum Discussion

visheshvats1's avatar
visheshvats1
Helper I
1 year ago
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 :    ...
Need Help
  • visheshvats1's avatar
    visheshvats1
    1 year ago

    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)



visheshvats1's avatar
visheshvats1
Helper I
1 year ago

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

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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.