I have a problem I have been stuck on, calculating an “Hourly Balance”. I will attach the report here. I am running into circular reference issues. What I could use help on is for one measure(or calculated column in the “Master” table. It is a monthly calculation and supposed to work like this:
Step 1. If the prior month hourly balance is positive then start the accumulation with the formula “Adj Prod less Target Hours”. (The answers are most often negative). I have this measure.
Step 2. If prior month Hourly Balance is negative then add this “Adj Prod less Target Hours” to the current months beginning balance.
If the prior step leads to a positive number then start over with step one and repeat. I continue to get circular reference errors when referring to prior month.
The correct answers for EmployeeID 2553 are:
Jun 2020 = -13
July 2020 = -.5
Aug 2020 = -15
Sep 2020 = -23.63
Oct 2020 = -19.13
Nov 2020 = -9.63.
Hi @Whitewater100. I’m having a little difficulty trying to match your description with your sample. For example, when you ask for [Current Month Beginning Balance], I’m guessing you mean the [Starting Deficit] measure, but this seems to be always blank for employee 2553/FY21. As to your statement
If the prior step leads to a positive number then start over with step one and repeat. I continue to get circular reference errors when referring to prior month.
I’m sure I’m missing something, but I cannot seem to find or generate any circular references either.
One of the techniques I’ve found most useful debugging complicated DAX measures is to create as many interim measures as possible, and add them all to a table to see that the interim steps are calculating as expected. This usually exposes the error, and, as a bonus, makes the final calculation logic simple (and I would use SWITCH TRUE logic instead of nested IF statements, as they’re easier to read).
Could you please give this a try, and if the issue persists, we’ll take another look?
Thank you for replying. This particular calculation has been unsolved for many weeks so it’s back here at the forum. In other, too unruly files lay many attempts. This is an example of calculatingSemi Cumulative Hourly Balance.xlsx (13.2 KB) extra pay so it looks quite strange if considered a straight payroll application.
Without any rules it is an easy calculation. Adjust Prod - Target Hours = Hourly Balance.
The main reason for the circular reference is the Hourly Balance ( I’ll say semi-cumulative hourly balance) is always dependent on the last month figure, in the same column. When a month’s result is negative it is considered for the next month’s calculation. A positive result can decrease the prior months negative balance but once an Hourly Balance turns positive it is an isolated result and the next months starts with this basic calculation above. Only negative final results of Hourly Balances can be accumulated for the next month.
I have attached an excel version on the desired results. I hope this excel document does a better job of explaining the issue.
Thanks for any assistance you or the forum might have on this one. I really appreciate it!!
Hi @Whitewater100. Unfortunately, the penny has not dropped for me re. the business rules. I’ve dropped a number of hours into your issue, and haven’t been able to come up with anything of value. As well, there are different figures shown in the correct values from your first post and the Excel values from your second post, which makes thing murkier. I’d fall back on my earlier suggestion, develop small easy interim measures and add them to your table to ensure they’re calculating correctly. Then use simple measure branching to mathematically add/subtract them according to your business rules.
For what it’s worth, here’s my rewrite of your “Hourly Balance R” measure using SWITCH TRUE logic:
Hourly Balance V2 =
-- the "Hourly Balance R" measure rewritten with SWITCH TRUE logic
-- gives same answers as "Hourly Balance R"
VAR _CurrentMonth = SELECTEDVALUE( Dates[Month & Year] )
VAR _StartingDeficit = SELECTEDVALUE( Master[Deficit] )
VAR _UsePositiveCO = SELECTEDVALUE( Master[Use Positive Carryover] )
VAR _ManualCO = SELECTEDVALUE( Master[Manual Carry Over Adj] )
VAR _AdjProd = [Adj Prod]
VAR _PMHourlyBalance = [PM Hourly Balance]
VAR _TargetHours = [Target Hours]
VAR _Result1 =
SWITCH( TRUE(),
_StartingDeficit <> 0, _StartingDeficit,
_ManualCO <> BLANK(), _AdjProd - _TargetHours + _ManualCO,
_UsePositiveCO = 1 || _PMHourlyBalance <= 0, _AdjProd - _TargetHours + _PMHourlyBalance,
_AdjProd - _TargetHours
)
VAR _Result2 = IF( ISBLANK( _CurrentMonth ), BLANK(), _Result1 )
RETURN
_Result2
Thank you for looking at this and I’m sorry you had too invest that time. It’s going on a month for me and it’s exhausting. lol.
I did change the last few rows in the excel document so you could see all the rules in one place as the example didn’t show you all the scenarios. What is wild is this formula works in excel.
=IF(O11=43952,C11,(IF(J11<>"",D11-E11+J11,IF(OR(H10=“Y”,L10<=0),D11-E11+L10,D11-E11))))
I’m going to try your work and I don’t think investing anymore time in this is a good idea. The issue is we have to always refer to the prior month ending balance(if negative) to figure out the current month ending balance. A preceding negative month end balance can go further negative or improve to be positive if Adj Prod - Target Hours for the current month when added changes the figure in either direction.
When a month’s end balance is positive, the person is paid and all is reset to “0” and the basic Adj Prod - Target Hours measure starts a new.
The starting deficit hardly ever occurs and it’s a one time thing. If it’s negative that is considered with the current months calculation(June 2020), if positive it has no influence on the future months.
It’s a real brain teaser! What a dumb calculation - but it does exist.
Thank you very much for replying so quickly and helping out on this particularly difficult calculation.
Hi @Whitewater100, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Whitewater100, we’ve noticed that no response has been received from you since the 8th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.