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

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)



Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,visheshvats1 
We are delighted that you have found a solution and are willing to share it.

 

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

 

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

 

Best Regards,

Leroy Lu