# Custom .Net Calculations SSAS

6 Reputation points
2023-06-23T15:19:38.3133333+00:00

I am seeking ideas on how to solve the following problem:

I want to give users the ability to use PowerBI and Excel to aggregate/filter data by "Investment Class" and return a measure which is the XIRR over the related transactions. To make it interesting I need to use my own custom XIRR algorithm (currently implemented in .Net). I would ideally like to be able to use PowerBI or SSAS Tabular for my semantic model to allow easy usage from Excel, PowerBI, Tableau etc.

Traditionally I have solved this problem using SSAS Multidimensional and a custom 'Stored Procedure' in the cube implemented in .Net to handle this calculation. I am hoping to accomplish this in a more modern cloud PaaS architecture that PowerBI or SSAS Tabular would provide.

Given that I can implement custom aggregations and TVFs, etc in SQL Server written in .Net I have explored using DirectQuery techniques, but this usually falls short when looking at multi-select dimension value scenarios.

Other solutions I am considering also include using non Microsoft MDX engines like Apache Kylin or Kyligence, which would provide the MDX interface with a cloud PaaS service, but would love to keep this in the MS realm if possible.

Hopefully this conveys my challenge. Ideally being able to use custom code in SSAS Tabular/PowerBI for calculated measures like we can in MDX would be a solve.

``````Create Table dim_Investment (
Investment_Id int not null Primary Key,
Investment_Class nvarchar(100) not null
)

Create Table fct_Transaction (
Investment_Id int not null,
Transaction_Date Date not null,
Transaction_Amount money not null
)

-- Example measure calc in SQL
Select
Custom_XIRR(f.Transaction_Date, f.Transaction_Amount, 0.1) As IRR
From
fct_Transaction As f
Inner Join dim_Investment As d On
(f.Investment_Id = d.Investment_Id )
Where
(d.Investment_Class In ('Pre IPO', 'Distressed'))
``````
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,265 questions