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

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
Super 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.

 

 

 

  • visheshvats1's avatar
    visheshvats1
    Helper I
    1 year ago

    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?

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      yes, you can add a filter to the above code that excludes days where [n] is zero.

       

       

       

       

      • visheshvats1's avatar
        visheshvats1
        Helper I
        1 year ago

        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: