Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gingerbread
Frequent Visitor

Circular Dependecy in Power BI DAX: measure depending on measure

Hello there,

we are working on recreating an Excel report with Power BI. We need to display expenses on a year by year basis:

gingerbread_4-1633607575721.png

We have one table in Power BI and the data is imported from DataVerse:

gingerbread_5-1633607702666.png

Certain Power BI measures depend on calculations of the previous year and this causes a circular dependency for the Series of payment (dynamic) 2022 measure.

gingerbread_3-1633607389516.png

 

This is an example on how to calculate the measures for year 2022:

 

Series of payment Static 2022 = Constant Sum

 

Series of payment (dynamic) 2022= Series of payment Static 2022 + Capital Costs 2022

 

Cumulative series of payment 2022 = Cumulative series of payment 2021 + Series of payment (dynamic) 2022

 

Capital Costs 2022 = Cumulative series of payment 2021 * Discount rate in %

 

Discount rate in % is a constant number.

 

As you can see we will have the Series of payments which consist of  data from one particular year. In this example we will choose 2022.

In the next step we will calculate the Series of payments (dynamic) of 2022 with the series of Payment static of 2022 and the capital costs. This is the cell expresion from excel:

gingerbread_0-1633606597139.png

 

 

In the next step we will calculate the cumulative series of payment of 2022 with cumulative series of payments from 2021 and their series of payments (dynamics 2022). Here we use the filter with Calculate to use the cumulative series which we calculated the year before in 2021.

gingerbread_1-1633606597142.png

 

 

With the capital costs or series of payments dynamics we run into a circular dependency, because power bi cannot detect that we want the value of 2021 which is already calculated.

 

gingerbread_2-1633606597145.png

 

I hope you can help us with a corect solution. We want to calculate the numbers dynamically, based on new years and new data.

These are the measures  we created in Power BI so far:

Capital Cost = CALCULATE([Cumulative series of payments], SAMEPERIODLASTYEAR(Tabelle[Year]))*0.09
Cumulative series of payments = [Series of payments (dynamic)]+CALCULATE([Series of payments (dynamic)],SAMEPERIODLASTYEAR(Tabelle[Year]))
Series of payments (dynamic) = CALCULATE(SUM(Tabelle[Series of Payments])+[Capital Cost])
 
 
3 REPLIES 3
gingerbread
Frequent Visitor

Sorry, the suggested solution does not solve the problem. The name for 'year'[year] is indeed misleading. Technically it is a date colum (ex '01.01.2021') formatted to show the year only.

amitchandak
Super User
Super User

@gingerbread , SAMEPERIODLASTYEAR takes date not year

example

Capital Cost = CALCULATE([Cumulative series of payments], SAMEPERIODLASTYEAR(Tabelle[date]))*0.09

 

change that can check

Hi @amitchandak ,

thank you, I will change that. For some reason power bi does not have any issues with integers.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.