Forum Discussion

visheshvats1's avatar
visheshvats1
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
1 year ago

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. 

Hope my question is clearer now.

lbendlin Ritaf1983 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago
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.