Row level Calculations for bps metrics

Question

Hi Alteryx Experts,

i have scenario like to create new field with required calculations and looking for some help.

IF product name contains (bps) then row should generate value with ((current month-previous year Dec month)/previous year Dec month)*10000 else row should generate same value what we have.

for example: bps metric has month has Feb that means calculation should be (Feb2023-Dec2022/Dec 2022)*10000 else Feb 2023 value

previous year row should be ignored once we generate new field by using formula.

I have attached sample data and i have similar data for 2022 and 2021(in this case current month would be last month of that year)

Can someone help me on this to get required data format for my reporting.

Thanks in advance.

Rama

Sample.xlsx

mukkapatir · Answer

Thank you for quick response.

This approach  working correctly when i have one bps product and when i add multiple bps products previous year numbers placing as same for multiple  bps products.

i was not able to attached sample data set with 2 years of data and for 2022 and 2023 you see 2 different Dec values which i suppose to keep for my reporting which is the reason i segregated data by years of actuals and target.

for example to calculate 2023 bps metric i should use measure as 2023 target and year as 2022  and month as Dec.

I have attached screenshot to show my sample data.

I hope this helps to provide solution.

Thanks a lot!

Rama

Scrreshot for sample.PNG

mukkapatir · Answer

Thank you for quick response.

This approach  working correctly when i have one bps product and when i add multiple bps products previous year numbers placing as same for multiple  bps products.

i was not able to attached sample data set with 2 years of data and for 2022 and 2023 you see 2 different Dec values which i suppose to keep for my reporting which is the reason i segregated data by years of actuals and target.

for example to calculate 2023 bps metric i should use measure as 2023 target and year as 2022  and month as Dec.

I have attached screenshot to show my sample data.

I hope this helps to provide solution.

Thanks a lot!

Rama

Scrreshot for sample.PNG

Pilsner · Answer

Here's a way I managed to get it to work, I hope I understood your calculation correctly. If you need to do it for a large data set across multiple years you would probably need a batch macro. Let me know how you get on:

Spoiler

Values Based on previous years Sales.yxzp