Need to Adjust Credits by Specific Amount based on what month is listed

DAX DAX Calculations
1

Hi Fellow Power BI Users -

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.

Capture

Let me know if you need more info.

Rose

2

Yep ok I understand this one. This very similar to the budgeting and forecasting techniques that I’ve showcased a few times.

The key here is comparing data at different granularities. Where you have you actuals (credits in this case) and also your adjustments

You can use here a technique I call ‘virtual relationships’ by using the TREATAS function.

You have the setup correctly. You need to have a stand alone table that just has the month and the adjustment amounts.

This table can be stand alone. It’s doesn’t need any relationships to anything.

See an example here from the latest learning summit demo model

image
image.png580×526 19.3 KB

The budget would be your adjustments

This is then the formula pattern you would use

BudgetAllocation = CALCULATE( AVERAGE( 'Budget Sensitivites'[Budget Sensitivity] ),
                            TREATAS( VALUES( Dates[Short Month] ), 'Budget Sensitivites'[Month Name] ) )

You would sub the AVERAGE out for your measure - Total Inv Credits

Then within the TREATAS you would have the month the corresponding Months from the Dates table and your Adjustments table.

See how you go with this.

This is exactly how you should do this.

If still can’t find a way add the pbix file here

3

Here is a review of the TREATAS function.

It’s a really great function for these types of things

4

Sam,
I tried your suggestion. All that did was return the original Invoice Credit amount.
Capture

I did find another solution using 2 measures that seems to work:

Total Credit Adjustment = IF(
     MAX( PBI_FSCAPF[PADFMO] ) = 3 ||
     MAX( PBI_FSCAPF[PADFMO] ) = 6 || 
     MAX( PBI_FSCAPF[PADFMO] ) = 9 ||
     MAX( PBI_FSCAPF[PADFMO] ) = 12 ,
            375000,300000)

Total Inv Credit Adjustment = IF(
     MAX( PBI_FSCAPF[PADFMO] ) = [Today Month],
     calculate(  [Total Inv Credits] + [Total Credit Adjustment] ) , 0)

Capture2
Capture2.JPG778×247 18.4 KB

There are several other caveats that I had to consider that I didn’t mention in my original post only because they were not causing the problem.

One was that I have 3 companies and the credit adjustment applies only to one company.
The second was that the total credit adjustment only is shown for the current month. If the month has passed, then the total credit adjustment needs to be zero.

Thank you for your help as always!!

Rose

5

Ok great, glad you got it working.

Maybe be of a mix up around how to setup the model when using TREATAS. The supporting table needs to be setup in a specfic way.

I think just use what you have right now, it is probably absolutely fine for what you need.

6

Anytime I get more DAX knowledge even if the DAX formula didn’t help in this situation, it’s a win-win!

Thank you!

Rose