Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to calculate a 3% inflation across 4 years, given I have a current value made up of a SUM of values.
My dax skills are weak.
I'm pretty sure I need to use PRODUCTX() to some degree but all my attempts are failing.
I have read several posts about this, but most refer to date field filter within a calculate statement? My "dates" are just year integers.
Maintenance costs are entered for current year (2018) and need to show inflation of 3% for 2019, 2020, 2021, 2022 (Compound interest)
Sample data is very simple, screenshot below.
Desired result is in a table (screenshot below) showing "$0" where there should be the previous year's value multiplied by 1.03.
I added a proper date column but was still unable to make this work.
I found a sample of doing this with a simple year column, but still am unable to make it work.
Using that sample, I came up with this code, where 'Pivoted' is the table, [Year] is the year, and [NULLMaintCost] is the column containing the maintenance cost for each product.
Maintenance Inflation = VAR MaxYear = MAX ( Pivoted[Year] ) VAR Cumulative = PRODUCTX ( FILTER ( Pivoted, Pivoted[Year] <= MaxYear ), 1.03 ) VAR Number = SUM ( Pivoted[NULLMaintCost] ) RETURN Number * Cumulative
It is calculating an amount for EACH product for 2018 (the only year where a maintenance cost is entered) but it actually should be the same amount. As well, the aggregation amount is way off.
Then it is not calculating anything for the following years, 2019, 2020, 2021, 2022.
@Anonymous,
Learn more about the filter context and then try adding the ALL Function.
Check out my "Time Intelligence The Hard Way" quick measures here:
I created these to do stuff like you are talking about when you don't have an actual Date field.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |