Forum Discussion
Monthly Rebate calculation help
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