Power BI Live Connection to SSAS - XNPV with separate data and date tables

Question

I tried to write a measure to calculate the XNPV of transactions from the data table, but it's giving me 2 problems:

1. It's not calculating correctly, just spitting out the same raw data as the data table, and
2. it won't even resolve if there's any blanks for any dates on the data table.

Here's my measure:

MyPV = 
XNPV(
    ADDCOLUMNS(
        VALUES('Dates'[Date]),
        "Transactions", 'TransData1'[Values]
    ),
    [Transactions],
    Dates[Date],
    1.0775
)

The SSAS environment is already configured to have separate date and data tables, but I want to avoid reinventing the XNPV wheel if possible, since that'll likely introduce lag.

I tested my measure on a few different transaction data sources, but it caused any visuals to error unless there were no blank values for the time period filtered.

Answer 1

Try it with SUMMARIZECOLUMNS which won't return any blank values.

MyPV A = 
  XNPV(
    SUMMARIZECOLUMNS(
      'Dates'[Date],
      "Transactions", MIN('TransData1'[Values]) // or 'TransData1'[Values] if measure
    ),
    [Transactions],
    [Date],
    1.0775
  )

Or filter out blanks:

MyPV B = 
  XNPV(
    FILTER(
      ADDCOLUMNS(
        VALUES('Dates'[Date]),
        "Transactions", 'TransData1'[Values]
      ), 
      NOT ISBLANK([Transactions])
    ),
    [Transactions],
    [Date],
    1.0775
  )