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)



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:

 



  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    There is no need for sorting the raw data. 

    • visheshvats1's avatar
      visheshvats1
      Helper I
      1 year ago

      Understood, and yes I could get the value using the expression share by you earlier. Thank you for that.
      But this only works if i filter the table with a given 'deal name'. Not for each deal. 

      Working Table dax: 

       

       

      But when i modify to accept any 'deal name':

      The subtotal is being repeated over and over: 

      Any idea on how to resolve this? Thank you.






      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Please provide sample data that fully covers your issue. In a usable form (not as screenshots).
        Please show the expected outcome based on the sample data you provided.