Accumulated inflation according to selected period.

[Syndicate_Admin]

Good day, community.

Currently I have a small problem. I need to get the real profit i.e. taking into account the inflationary component.

Roughly I have a graph of lines showing the evolution of sales, and having information when selecting the sales of June 2018 the sales of the previous period (June 2017) and also the respective variation ej 94% more income. But this information is incredibly biased by inflation. I have a table with the inflation of every month since 2017. But I can not make the sum of the accumulated, since it always takes as a base the initial year 01/2017. I need a measure that when selecting the month of June 2018, brings me the sum accumulated since June 2017, not from the first date.

There is also another problem and that is that for this you should recalculate the accumulated inflation with the following form eg for June: ((1 + inflation June) * 1) -1 , accumulated to July ((1 + inflation July) * (accumulated inflation June + 1) )-1 , accumulated to August ((1 + inflation August) * (accumulated inflation July + 1))-1 .

That is, when selecting a month of 2018, make an accumulated sum with that formula from the same month of 2017 to the selected one.

I tried a thousand ways and I do not get any accurate results, I use this message as a last resort, I would really appreciate a help!

[Ashish_Mathur]

Hi,

In an MS Excel workbook, sare some data and show the expected result (with your formulas).  I will try to translate those formulas in DAX/M of PowerBI.

[v-rongtiep-msft · Accepted solution]

Hi @Syndicate_Admin ,

To calculate the real profit taking into account the inflationary component, you can create a measure that calculates the accumulated inflation from the selected month of 2017 to the selected month of 2018. You can use the following formula to calculate the accumulated inflation for each month: ((1 + inflation of the month) * (accumulated inflation of the previous month + 1)) - 1.

To create this measure, you can use the DAX function "CALCULATE" to filter the data by the selected month of 2017 and the selected month of 2018. Then, you can use the DAX function "SUMX" to calculate the accumulated inflation for each month. Finally, you can use the DAX function "MAX" to get the accumulated inflation for the selected month of 2018.

Here is an example of the DAX formula you can use:

Real Profit = 
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
VAR InflationTable = 'InflationTable'
VAR AccumulatedInflation = 
    SUMX(
        FILTER(
            InflationTable,
            InflationTable[Year] = 2017 && InflationTable[Month] >= 6 ||
            InflationTable[Year] > 2017 && InflationTable[Year] < SelectedYear ||
            InflationTable[Year] = SelectedYear && InflationTable[Month] <= 6
        ),
        ((1 + InflationTable[Inflation]) * (CALCULATE(MAX(InflationTable[Accumulated Inflation]), FILTER(InflationTable, InflationTable[Year] = EARLIER(InflationTable[Year]) && InflationTable[Month] = EARLIER(InflationTable[Month]) - 1)) + 1)) - 1
    )
RETURN
    SUM('Table'[Sales]) / (1 + AccumulatedInflation)

In this formula, 'Table' is the name of the table that contains the sales data, and 'InflationTable' is the name of the table that contains the inflation data. The formula calculates the accumulated inflation for each month from June 2017 to June 2018, and then divides the sales by the accumulated inflation to get the real profit.

Please note that you need to adjust the table and column names in the formula to match your data model.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.