Use measure values on table

[visheshvats1]

I have a fully operational measure that works when I slice the page by a particular Deal Name. I wish to build a table visual with all deals and their respective XIRR values. How do i modify my measure? 

My measure: 

XIRR =
VAR Trantype = {
"Investments Receivable",
"Bond - interest receivable",
"Purchase: security conversion",
"Investment Property",
"Return of Excess Contributions"
}
VAR __Table = UNION(
SELECTCOLUMNS(
FILTER(
'General Ledger',
'General Ledger'[Deal Name]= SELECTEDVALUE('General Ledger'[Deal Name])&&
'General Ledger'[GL Date] >= MIN('Date Table'[Date]) &&
'General Ledger'[GL Date] <= DATE(2024,8,31) &&
'General Ledger'[Trans Type] IN Trantype
),
"GL Date", 'General Ledger'[GL Date],
"Net", 'General Ledger'[Credits only] - 'General Ledger'[Debits only]
),
ROW(
"GL Date", DATE(2024, 8, 31),
"Net", [Net Sheet 1]
)
)
RETURN
XIRR(__Table,[Net],[GL Date])

[_AAndrade]

Hi @visheshvats1,

Please try this measure:

IRR All Deals = 
VAR Trantype = {
    "Investments Receivable",
    "Bond - interest receivable",
    "Purchase: security conversion",
    "Investment Property",
    "Return of Excess Contributions"
}
VAR DealTable = VALUES('General Ledger'[Deal Name])
VAR __Table = UNION(
    SELECTCOLUMNS(
        FILTER(
            'General Ledger',
            'General Ledger'[Deal Name] IN DealTable &&  
            'General Ledger'[GL Date] >= MIN('Date Table'[Date]) &&
            'General Ledger'[GL Date] <= DATE(2024,8,31) &&
            'General Ledger'[Trans Type] IN Trantype
        ),
        "GL Date", 'General Ledger'[GL Date],
        "Net", 'General Ledger'[Credits only] - 'General Ledger'[Debits only]
    ),
    ROW(
        "GL Date", DATE(2024, 8, 31),
        "Net", [Net Sheet 1]
    )
)
RETURN
    IF(
        COUNTROWS(__Table) > 1,
        XIRR(__Table, [Net], [GL Date]),
        BLANK()  
    )

[visheshvats1 · Accepted solution]

Thanks for the prompt reply. But, it gave the same error. 

I just found the solution, the problem was that the Guess Value and Alternate Result was supposed to be added to the XIRR function. As it was unable to calculate XIRR for some of the Deal Names in the table. 

Another change was removing the SELECTEDVALUE filter, for totals to show up in the the table. 

My new Measure: 

}

VAR __Table = UNION(

    SELECTCOLUMNS(

        FILTER(

            'General Ledger',

            'General Ledger'[GL Date] >= MIN('Date Table'[Date]) &&

            'General Ledger'[GL Date] <= DATE(2024,8,31) &&

            'General Ledger'[Trans Type] IN Trantype

        ),

        "GL Date", 'General Ledger'[GL Date],

        "Net", 'General Ledger'[Credits only] - 'General Ledger'[Debits only]  

    ),

    ROW(

        "GL Date", DATE(2024, 8, 31),  

        "Net", [Net Sheet 1]

    )

)

RETURN

XIRR(__Table,[Net],[GL Date],0.1,0)