Monthly Rebate calculation help

[Anonymous]

When UTP hits $300,000 in "Total Year to Date" sales, we need to book 3% of that total over $300,000.  November works fine on the image I included.  The issue is December.  I need the result to not include the $535 from November that I already accounted for.  The correct December "UPT Rebate amount" should be $26,217, which equals "Total Sales minus Credits" for that month since we went over the $300,000 in November.  The correct UPT Booked should be $787, which is 3% of $26,217.  I hope this all makes sense to someone.  Thank you!

I think I need the "UPT Rebate amount" formula to read something like......if(([Total Year to Date]-300000>0,([Total Year to Date]-300000)-last months UTP Rebate amount,0)

Current measures:

UTP Rebate amount = if(([Total Year to Date]-300000)>0,[Total Year to Date]-300000,0)

UTP Booked = [UTP Rebate amount]*.03

[v-sihou-msft]

@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

[Anonymous]

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)

[dkay84_PowerBI]

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.

[Anonymous]

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