Hi all,
I'm looking to create a measure in PowerPivot that takes a customers base rate and applies a percentage increase (based on CPI). Depending on the financial years selected in the slicer the measure would 'compound' the rates.
Ideally I'll end up with a pivot table that looks like the below with the ability to chop and change financial years, there will always be the current FY and the next FY selected but we need the ability to look back as well.
My data is made up of a few tables:
Customers (Customer, Region, Country)
CustomerRates (Customer, Product, Rate, Date for base rate)
CPI (Customer, Date, CPI Rate) *Unfortunately the rate isn't applied consistently across all customers
Dates (Date, Month, Year, FQtr, Financial Year)
I need the ability to easily change the filter on the customer and financial years.
My struggle is that if say FY 2020 is selected, I need to look at the date of the base rate and apply any increases between then and FY 2020.
Is this possible to do in PowerPivot or am I flogging a dead horse! The reason I'm trying to do this in PowerPivot is because the data needs to be manipulated in a number of ways for other purposes (profit and loss statement, cashflow forecasting and to create customer invoices). There are a few more tables in the background but I've just included those relevant to the current 'hurdle' I'm trying to overcome. Any pointers would be greatly appreciated.
Thanks
Jo
I'm looking to create a measure in PowerPivot that takes a customers base rate and applies a percentage increase (based on CPI). Depending on the financial years selected in the slicer the measure would 'compound' the rates.
Ideally I'll end up with a pivot table that looks like the below with the ability to chop and change financial years, there will always be the current FY and the next FY selected but we need the ability to look back as well.
My data is made up of a few tables:
Customers (Customer, Region, Country)
CustomerRates (Customer, Product, Rate, Date for base rate)
CPI (Customer, Date, CPI Rate) *Unfortunately the rate isn't applied consistently across all customers
Dates (Date, Month, Year, FQtr, Financial Year)
I need the ability to easily change the filter on the customer and financial years.
My struggle is that if say FY 2020 is selected, I need to look at the date of the base rate and apply any increases between then and FY 2020.
Is this possible to do in PowerPivot or am I flogging a dead horse! The reason I'm trying to do this in PowerPivot is because the data needs to be manipulated in a number of ways for other purposes (profit and loss statement, cashflow forecasting and to create customer invoices). There are a few more tables in the background but I've just included those relevant to the current 'hurdle' I'm trying to overcome. Any pointers would be greatly appreciated.
Thanks
Jo
