Forum Discussion

visheshvats1's avatar
visheshvats1
Helper I
1 year ago
Solved

Use measure values on table

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])

Need Help
  • visheshvats1's avatar
    visheshvats1
    1 year ago

    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)

2 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

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

      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)