Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello there,
we are working on recreating an Excel report with Power BI. We need to display expenses on a year by year basis:
We have one table in Power BI and the data is imported from DataVerse:
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.
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:
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.
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.
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:
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.
@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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |