Calculate measure: sum of current year value and percentage of value coming from previous year

[P_D_G]

Hi all,

I have a filter (P) where user can select a value between 0-100%.
I also have a table with 2 columns A) year and B) value.

I have to split B into 2 new values based on P as follows:

- we push the result of P * B back to next year - column "C" in table below

- we keep the result of (1-P) * B for current year - column "Corrected B" in table below

However, in the following year we need to add the result of P*B from previous year to the current B and then multiply this result with P. And again:

- (1-P)*B will be the new result for the current year while

- P*B goes to next year to be added to B.

This method should be applied for each year - the last year (2025 in this example) is different as it should cummulate all the remaning parts, no need to multiply it with P.

Below is a data sample:
P = 40%

I've also added Columns "C" & "B(current year) hope it helps you better understand the calculation method.

A	B	C	B(current A) + C(previous A)	Corrected B
2020	1,000,000	400,000	1,000,000	600,000
2021	2,500,000	1,160,000	2,900,000	1,740,000
2022	5,000,000	2,464,000	6,160,000	3,696,000
2023	3,000,000	2,185,600	5,464,000	3,278,400
2024	4,000,000	2,474,240	6,185,600	3,711,360

Corrected B is the measure I'd like to achieve, but I couldn't get my head around this issue so far.

Do you have any recommendation?

Thanks in advance!

[v-shex-msft · Accepted solution]

HI @P_D_G,

AFAIK, current power bi does not support to create a dynamic calculate column/table based on filter/slicer selections, please use measure formula which works on the same data level of filter/slicer to achieve the dynamic result that interaction with selections.

According to your request, it sounds like you are try to achieve recursion calculation based on its calculation result in previous row contents. This also not works on DAX formulas.
Regards,
Xiaoxin Sheng

[P_D_G]

Hi @v-shex-msft,

yes, I definitely want to create a measure - the table is simply the representation of the required output.

Thanks for your answer, I was hoping for a different one though, but no wonder it's not possible.

Thanks!

[Fowmy]

@P_D_G 

Can you do the calculation for CORRECT B in Excel and share the formula?
I am getting the following values showing variance

2020	600,000
2021	1,740,000
2022	3,600,000
2023	3,000,000
2024	3,120,000

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[P_D_G]

@Fowmy

here is the link to the google doc file with the calculations.

Thanks,

[amitchandak]

@P_D_G , see if time intelligence with date calendar can help

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[P_D_G]

Hi @amitchandak,

I'm afraid this is not hte solution I've been looking for - but appreciate your answer and the suggested materials.