Power Pivot Drama…the Saga Continues
So, I don’t know about you, but in as much as Power Pivot is super cool, it can be super stressful as well!
I am building an automated valuation and reporting tool for a client. They require the model to report on their Bonds, Money Markets, Treasury Bills, Capital Markets, Real Estate, Cash Accounts and Dividend Receivables.
The model consists of several fact files and dimension files totaling about 15. I am having fun, to say the least.
Initially, I built the model using Excel 2016 but had to transfer it to Excel 2013. That brought about a bit of “confusion” to my model. My goal is to keep you updated on challenges I have had in my Power Pivot journey and how I resolved them.
CONFUSION 1: MAX and MIN works as an Aggregator but not as a “normal” Excel MAX AND MIN function
So, as you know, MAX and MIN are aggregators in DAX formulas. I had a formula for calculating holding days of a Euro Bond that uses a 30/360 convention as follows:
Random Side Note: Formula Formatting is compliments of www.daxformatter.com for more “eye-friendly” DAX formulas. Which one looks better? The above or……
= MAX(30 – DAY(ValueDate), 0) + MIN(30, DAY(Rdate)) + 360*(year(Rdate) – year(ValueDate)) + 30*(MONTH(Rdate) – MONTH(ValueDate) – 1)
You can format your formula on the website with the click of a button and then copy and paste it into your Model.
Now, back to my story…
In Excel 2016, this formula acted “responsibly”.
BUT…..Excel 2013, rejected it.
2013 said, “I won’t agree”.
I asked 2013, “Why? What was my offence?”
2013 said, “You didn’t pay homage, therefore MAX and MIN as Non-Aggregators are forever banned from the 2013 kingdom”
I pled. I cried. I fasted. I prayed. To no avail.
Though the fasting and praying did not bring about a change in 2013’s mind, it did bring about a revelation.
What was that revelation?
=IF() BABY! (Please note, Baby is not included in the formula!)
Modified 2013 Approved Formula:
- Do not negotiate with terrorists AKA Excel 2013!
- When there is a will, there is a way AKA learn to think outside of the box!
- Always confirm the version of Excel that will be used by the client before building a gangster model AKA ask the right questions ahead of time!
Stay tuned for CONFUSION Number 2.