I have a table that lists customer credits by month. My credits come from my fact table and the month comes from my date table. Easy enough. My challenge is that the credit amount for any given month needs to be adjusted by a specific amount based on what month it is.
If the month is the 3rd, 6th, 9th, or 12th month, then the adjustment needs to be -$375,000, otherwise it needs to be -$300,000.
Example: Using 2017-01 and 2017-03 for reference. My credits for month 1 are -$389,044, the adjustment would be -$389,044 + $300,000 = $89,044 total credits.
But if my credits for Month 3 are -$294,903, then the adjustment would need to be $294,903 - $375,000 = -$80,907.
I tried to accomplish this by creating a fact table that lists the month # and the adjustment amount for that month, but I cannot figure out how to apply the credit adjustment. All of my measures thus far haven’t worked.
I don’t want to SUM the adjustments, I just need to look that value up based on what month it is. I tried to use LOOKUPVALUE, but cannot get that to work.
Let me know if you need more info.