Many to many relationship in Power Pivot (Excel data model) - Replicate Power BI behavior in Excel

users27m1 0
Viewed 28

In power BI, I can use a calendar with LTM (last 12 months) and link it to a fact table (Sales for example). There are multiple transactions in the Sales table on the same date (to multiple customers) and there are multiple Periods linked to the same date (e.g. LTM and FY2022).

Calendar

Sales

The relationship is many to many and it's perfectly managed with power BI

PBI DM

Simple PBI table

Note the total is different than the sum of the single rows (correctly)

I would like to replicate the same in Power Pivot, but I can't find a good solution.

Power Pivot Error message

Any suggestions? Thanks!

powerpivot data-modeling many-to-many powerbi excel
1 Answers

You can get rid of the many-to-many by using a proper date table.

In Power Pivot add a new date table, and add columns for FiscalYear, and IsLastTwelveMonths, with DAX calculated columns.

David Browne - Microsoft0
Related