Forum Discussion
Monthly Rebate calculation help
Anonymous
In this scenario, since your rebate is based on YTD amount, you can create a calculated column to have the current Rebate minus last month Rebate. Please refer to formula below:
actual Rebate = Table1[Rebate]-CALCULATE(SUM(Table1[Rebate]),FILTER(Table1,Table1[Month]=EARLIER(Table1[Month])-1))
Then just calculate the booked value based on above "actual Rebate" column:
Regards,
Simon Hou
I'm having problems figuring this out. At no time am I using [Month] in any of my calculations, nor do I have a column "month" in my Invoice table. My invoice tables Post_Stk_date column is linked to my DateDimension table. Below is my current measures.
Total Sales = sum(Invoice[Total])
Total Sales minus Credits = [Total Sales]-sum(RMA[Credit Memo])
Total Year to Date = TOTALYTD( [Total Sales minus Credits] , DateDimension[Date])
UTP Booked = [UTP Rebate amount]*.03
UTP Rebate amount = if(([Total Year to Date]-300000)>0,[Total Year to Date]-300000,0)
The reference to [Month] was from the fictional table that the previous post used to solve your question. You obviously update your formula with your table/column names.
In your screenshot, you have a column for month even if your YTD calculation used date (when you put YTD and Month Name in the table, it will filter all Dates for each month, so you get the aggregated YTD for each month).
If the formula given earlier doesn't work, try making a calculated table using SUMMARIZE to calculate your various metrics by month so that they all reside in one table and perhaps the formula given will work.
I hate being "that guy" but I am having all kinds of issues. When I get up to the "Earlier" part of the formula, it is asking for "Column Name" and I think I want to enter DateDimension[MonthFull],-1 but it is not accepting anything. I get the error below.
Here is without the comma
