We've been trying to use the XIRR function in PowerBI, and trying all sorts of things over the better course of a month to get it to work. And we did, finally, get the XIRR function to stop erroring out. And it returns IRRs that seem reasonable enough for our test data for some IDs, but others look way too high.
I'm not looking for help with the XIRR function itself. As the first parameter of XIRR is a table, I'm wondering if there's a way to preview the Calculated Table that PowerBI will assemble using DAX and unions, so I can see what the raw data is passed into the XIRR DAX function.
As I said, I'm not looking for help on XIRR, but if it helps in any way, here's sample code for what we're trying to implement so far (parts between ** form the table I want to preview, line by line):
XIRR Test1 =
CALCULATE (
XIRR (
**UNION(
FILTER(
FILTER(
SELECTCOLUMNS(
'TestTransactions',
"Date", RELATED('Dim Date'[DateValue]),
"Amount", 'TestTransactions'[TransactionAmount]
),[TestFilter] <> 1),[Date] < MAX('Snapshot Table'[As Of Date])),
FILTER(
FILTER(
SELECTCOLUMNS(
'TestTransactions',
"Date", RELATED('Dim Date'[DateValue]),
"Amount", 'TestTransactions'[TransactionAmount]
), [TestFilter] = 1),[Date] = MAX('Snapshot Table'[As Of Date]))
)**,[Amount],[Date]))
I've tried just creating a different page in the PowerBI report and filtering the TestTransactions table down to what I'd expect it to be bringing in, but the XIRRs from that data does not match what's on the first page, so either the table being passed into XIRR as a parameter isn't as I thought, or there's more to XIRR.
