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
Anonymous
Not applicable

[DAX] Calculate steady inflation (ProductX???)

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.

 

 

sampledata.PNGdesiredresult.PNG

3 REPLIES 3
Anonymous
Not applicable

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.

https://community.powerbi.com/t5/Desktop/Help-with-DAX-measure-for-cumulative-percentage-calculation...

 

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.

 

aggregation.PNGdetail.PNG

 

@Anonymous,

 

Learn more about the filter context and then try adding the ALL Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

Check out my "Time Intelligence The Hard Way" quick measures here: 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

I created these to do stuff like you are talking about when you don't have an actual Date field.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.