Need to adjust credits by specific amount based on what month it is

[Roseventura]

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 matching my DATE month to the Credit Adjustment month.

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.

Rose

[v-yuezhe-msft · Accepted solution]

@Roseventura,

Please create the following measure in your date table.

Adjustment = IF(MAX('Date'[Month]) =3 ||MAX('Date'[Month]) =6 || MAX('Date'[Month]) =9||MAX('Date'[Month]) =12 ,375000,300000)

Create the following measure in your fact table. For more details, please review the attached PBIX file.

changecredit = SUM(Table1[Credits])+[Adjustment]

If the above dax doesn't return your expected result, please share sample data of your table following the guide in this thread:https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.


Regards,
Lydia

[Roseventura]

Lydia,

This worked beautifully!  Thank you for your help!

Rsoe