I was not able, so far, to receive any input from the community. I have taken the file further but am hung up on a couple of things. When convenient would you be able to check it out? I included the example result and a good definition of the conditional logic.
Hi @Whitewater100 (Bill). I took a run at this, but don’t fully understand the issue from your examples and the notes on you “Overview” page. I’ve got to get back to the day job, but will try again tomorrow. Sorry for the delay.
Greg
Thank you for replying. It’s a pretty wild example so I get why it looks confusing. The excel formulas(two) that I’m trying replicate as both calculated columns and DAX measures are in column L & M on final pay calc tab on excel file (tab also highlighted in green.)
My new attached files are the same as the originals but the pbix file reflects the work I was doing today.
Because there are nearly 10 conditional statements in the “Current Hourly Balance” (excel column L)column it makes it drawn out. Once that column is figured out the wages should be easier as it’s positive hour result times hourly wage. No OT or weekends to consider. This scenario is on the monthly level for the roll up and final results.
I’ve been challenged because in certain cases we have to look at the prior month ending balance to add the negative (or positive) monthly hourly balance for the current month to.
Each employee has a target number of hours to work per day (rolled up to monthly) the difference between these two (ACTUAL - TARGET) is "Adjusted Target. Actual hours worked are (ADP regular hours + sick, vacation + other). So you’ll see the Adjusted Target result in one of my calculated columns in the Hours Table. That’s the main driver but there all those other conditions that makes it more complicated. The excel formula in column L is specific to these conditions. The formula is not intuitive.
I have taken three Employee ID’s to base this example on. #5,9 & 12. are in the .pbix
I replicated the approach in your solution video as far as the model set up goes.
Essentially the question is how to replicate the two excel formulas in column L & M in both calculated columns and by DAX measures. I did as much as I could so far. I hope all this writing helps a bit.
Thanks again Greg for looking at this. I really appreciate this.
