It’s great to know that you are making progress with your query @dsiffredi. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Are you sure the people specifying the requirements aren’t just messing with you at this point? 
Let me take another crack at this tomorrow and get back to you. I don’t think implementing the requested shift will be that difficult.
- Brian
1 Like
OK, Take #3. I just had to think for a bit about what changed and what didn’t relative to scenario #2 above. Here’s how I revised our prior solution:
-
In Power Query, created a new field called Period Plus One by using Split Columns to strip off the last character of the Period field, incremented it upwards by one, and then merged the two columns back together
-
changed our Spread Revenue measure from the simple
[Total Opportunity Revenue] * [Lookup Scaling Factor]
to the more complex construct below, which traps the boundary condition where the opportunity is realized in P12 and has nowhere left to "spread’ (if this were a multi-fiscal year model, we could just roll P12 over to P1, with FY incremented by one). We could have done this as an IF statement, but I chose SWITCH( TRUE(), instead because a) it’s just a cool function; and b) it’s easy to add to if the client imposes any additional requirements.
Spread Revenue =
SWITCH( TRUE(),
SELECTEDVALUE( Opportunities[Period] ) = "P12" , BLANK(),
[Total Opportunity Revenue] * [Lookup Scaling Factor]
)
Now the Spread Revenue with Totals measure is unchanged, due to the magic of Measure Branching, since it calls the measure above for the relevant non-total cells. For the totals, we just need to change the evaluation context in the visuals by substituting Period + 1 for Period in the Rows well of the matrix:
Once we do that, we are back in business again:
Please give this a thorough look to make sure that I interpreted the new requirement correctly, and give a shout if you have any other questions. Full solution posted below.
Best,
- Brian
eDNA Forum - Revenue Spread Solution3.pbix (118.1 KB)
1 Like
Hi Brian, sorry it took me a while to get back to you. I’m still stuck on this. I have a meeting to make sure we are all on the same page in terms of requirements and will be take another stab at this later this week. I’ll let you know if the solution provided works or If I still need to keep working at this.
thanks so much again for your help. very much appreciated.
have a great week!
Cool – thanks for the update. I definitely want to make sure you have a properly working solution on this one, so once you get some additional clarity on the requirements and check out the revised solution I provided above, just give a shout if we need to keep tinkering with this. I think I’ve built that in such a way that it should be fairly easy to modify if requirements change. Good luck with the meeting!
- Brian