BTW I have a datekey table that connects Periods between sales pipeline (where the new works sold is being tracked) and the revenue conversion rates little table with the % over time.
Thanks – that’s really helpful. I think I’ve got the problem framed properly now. This is a really interesting one. I think it will all come down to structuring the revenue spread table in a way to make it easy for DAX to access the proper scaling factor. I’ll get cracking on this, and hopefully be back to you soon…
OK, here goes… This one ended up being really interesting - here’s what I did:
As anticipated, setting the supporting revenue scaling factor table up correctly makes it much simpler. I took your Revenue Conversion Rates Table and unpivoted all the month columns (and added a fiscal year column to make the solution more “durable” over time). Supporting table now looks like this:
Now the measure to look up the proper scaling factor becomes a pretty straightforward CALCULATE/FILTER construct, filtering on fiscal year, period and short month:
Lookup Scaling Factor = VAR SelMo = SELECTEDVALUE( Dates[ShortMonth] ) VAR SelPer = SELECTEDVALUE( Opportunities[Period] ) VAR SelFY = SELECTEDVALUE( Dates[FYNum] ) VAR ScalFctr = CALCULATE( SELECTEDVALUE( 'Revenue Conversion Rates'[Scaling Factor] ), FILTER( 'Revenue Conversion Rates', 'Revenue Conversion Rates'[Scaling Factor FY] = SelFY && 'Revenue Conversion Rates'[Period] = SelPer && 'Revenue Conversion Rates'[Short Month] = SelMo ) ) RETURN ScalFctr
Then scaling the revenue becomes a very simple matter of multiplying revenue by the appropriate scaling factor(s):
Spread Revenue = [Total Opportunity Revenue] * [Lookup Scaling Factor]
So far, so good. Just drop this measure into the matrix and…[cue sad trombone sound]
The individual cells calculate correctly, but the totals do not since they don’t have sufficient evaluation context to calculate the correct scaling factor(s). So now we have to create a measure with a virtual table of all combinations of period and short month (using CROSSJOIN) and calculate the scaling factor and scaled revenue for each row of that table. Then there are four conditions to evaluate:
- both period and short month have one value - skip the virtual table and just use the measure above
- short month has one value, but period does not (column totals) - SUMX of the virtual scaled revenue column over all values of period
- period has one value, but short month does not (row totals) - SUMX of the virtual scaled revenue column over all values of short month
- neither field has one value (grand total) - straight SUMX of virtual scaled revenue column
Here’s the measure implementing this logic:
Spread Revenue with Totals = VAR SelMo = SELECTEDVALUE( Dates[ShortMonth] ) VAR SelPer = SELECTEDVALUE( Opportunities[Period] ) VAR SelFY = SELECTEDVALUE( Dates[FYNum] ) VAR ScalFctr = CALCULATE( SELECTEDVALUE( 'Revenue Conversion Rates'[Scaling Factor] ), FILTER( 'Revenue Conversion Rates', 'Revenue Conversion Rates'[Scaling Factor FY] = SelFY && 'Revenue Conversion Rates'[Period] = SelPer && 'Revenue Conversion Rates'[Short Month] = SelMo ) ) VAR vTable = ADDCOLUMNS( CROSSJOIN( VALUES( Opportunities[Period] ), VALUES( Dates[ShortMonth] ) ), "Factor", [Lookup Scaling Factor], "SprRev", [Spread Revenue] ) VAR TotSpredRev = IF( HASONEVALUE( Opportunities[Period] ) && HASONEVALUE( Dates[ShortMonth] ), [Spread Revenue], IF( HASONEVALUE( Dates[ShortMonth] ), CALCULATE( SUMX( vTable, [SprRev] ), VALUES( Opportunities[Period] ) ), IF( HASONEVALUE( Opportunities[Period] ), CALCULATE( SUMX( vTable, [SprRev] ), VALUES( Dates[ShortMonth] ) ), SUMX( vTable, [SprRev] ) ) ) ) RETURN TotSpredRev
Now drop this in the matrix values well, and boom… now all totals work just as expected
Now your visuals should be easy to generate from this matrix and the above measures.
Really fun problem. Enjoyed working with you on this – hope it’s helpful. Full solution file posted below.
eDNA Forum - Revenue Spread Solution.pbix (120.7 KB)
Brian, I wasn’t even thinking you could do that this quickly. I’ll take a look right now and see how that works on my model. I’ll let you know my findings, thank you SOOOO much for giving me such a hand .
My pleasure. Just give a shout if you have questions or we need to tweak it for your model.
I’ve found an issue and right now I’m stuck (12 am here so there’s the fact that I’m also tired).
the issue is that the first measure works (lookup scaling factor) IF there’s no relationship between date table and opportunities table (sales pipeline in my model) , but in my model there is a relationship and as a result when I was troubleshooting it the problem shows like this. I’m for this part rendering SelPer as the result of Lookup Scaling Value
and in reality in your example it does this.
but if you create a relationship (say for the sake of this example based on period since opportunities table doesn’t have dates just periods).
BTW: The calculated column for Opportunities table goes like this (hoping saves you time)
Period = “P” & IF(Dates[FM Number]< 10, 0 & Dates[FM Number], Dates[FM Number])
then you will see the same effect
any thoughts on how to overcome this? should I use an calculate all statement - if yes how and where. :-s
thanks again Brian. happy to do a remote session sharing desktop if this helps to troubleshoot with the live environment so we don’t go back and forth with test . I’m in US WestCoast (pacific timezone).
I though I figured it out but something is still off.
I’ve changed this variable calculation to get the same periods accross the short months
VAR SelPer = CALCULATE(SELECTEDVALUE( ‘Sales Pipeline’[Period]),ALL(‘Sales Pipeline’[Period]))
and that seemed to work on the testing
but when calculating the final scaling factor I’ve got these weird results.
Not even close with the “original table”
BTW: We can reproduce the issue with your PBI file if we set the relationship from dates to opportunities using period
What should the relationship between the Date table and the Opportunities table be? The relationship isn’t needed for this calculation, since the Lookup Scaling Factor measure is basically a three-dimensional version of the LOOKUPVALUE function, which operates fine on disconnected supporting tables. But, I’d like to build our solution simulating your actual model as closely as possible.
good question: we use the relationship to filter dates in this case I missed to add opportunity due date in the xls file (I honestly didn’t think it will mess up anything but it did).
opportunities table is sales pipeline in the actual model.
relationship is many to one , single
I’m not understanding the need for DateKey. Why not just use Dates[Date] linked with Opportunity [Opportunity Due Date]? Avoids the creation of an extra key field, and Dates[Date] will validate when you mark the Dates table as a date table.
Also, can you please provide me the opportunity due dates for the sample data? Just want to be sure we are working off the same example data.
I know , I know Brian … trust me if I could get rid of that relationship I would. but the end goal is to pull data from this view and add it to backlog (another table) and datekey is key to keep things talking to eachother . I wish I could even duplicate the table, get rid of the relationship and be done. but I can’t .
I feel your frustration, I’ve been working till almost 1am my time (what I’m trying to say is that I truly appreciate your help).
attached the file updated with the due dates in it.
Also , I’ve just checked the pre-reqs and I assumed one thing wrong. when we sell say in p6 we need to apply p7 % because we won’t be getting anything that month. something I / we can work out later.
Revenue Spread Over Time EnterpriseDna.xlsx (13.2 KB)
last but not least and I’m not sure if this is the right way to do it ( so you can try this way and see if that’s the right way to do it)
I’ve used allexcept to calculate SelPer and that removed the issue when the relationship is active.
VAR SelPer = CALCULATE(SELECTEDVALUE( ‘Sales Pipeline’[Period]),ALLEXCEPT(‘Sales Pipeline’,‘Sales Pipeline’[Period]))
Lookup Scaling Factor = VAR SelMo = SELECTEDVALUE( DateKey[Month] ) VAR SelPer = CALCULATE(SELECTEDVALUE( 'Sales Pipeline'[Period]),ALLEXCEPT('Sales Pipeline','Sales Pipeline'[Period])) VAR SelFY = SELECTEDVALUE( DateKey[FY] ) VAR ScalFctr = CALCULATE( SELECTEDVALUE( 'Revenue Conversion Rates'[Scaling Factor] ), FILTER( 'Revenue Conversion Rates', 'Revenue Conversion Rates'[Scaling Factor FY] = SelFY && 'Revenue Conversion Rates'[Period] = SelPer && 'Revenue Conversion Rates'[Short Month] = SelMo ) ) RETURN ScalFctr
and to get spread revenue I have to use allexcept again to get the full table.
still learning PowerBi and this example is really tricky , thanks for your support.
here’s the relationship between backlog and pipeline (opportunities in our example)
Had to think about this for a bit, but it turns out there’s a quick and easy workaround to the requirement of having to link the Opportunities table to the Date table. So, to make this solution work, we need a disconnected supporting table with values for Short Month, in order to do the proper lookup from the Revenue Scaling Factor table. In the initial solution I provided, I used the Date table, which worked fine until we had to impose the requirement of the relationship. However, after that relationship was created, we still have another disconnected supporting table with the same information needed for Short Month - the Fiscal Month to month supporting table I created initially to properly sort the Short Month field in the matrix header. Just do a search and replace in the measures, substituting Fiscal Month[Short Month] for Dates[Short Month], and then do the same substitution in the visuals, and it’s back to running like a champ:
Hope this gets you what you need, but give a shout if not. Full revised solution posted below.
eDNA Forum - Revenue Spread Solution2.pbix (111.5 KB)
Simply put. YOU ARE DA MAN!!!
My god , you have no idea how difficult this thing was for me to even start thinking about . I’ll give it a try before I can call it case closed but it’s pretty darn close to be solved.
thank you so much, you won’t believe how much I’ve learned from this. how to think things thru and rethink others. Still need to figure out a few things , but trust me when I say I’m very thankful for your help.
Take care and stay safe.
Oh man, I know that feeling exactly. When I’ve been stuck like that, other folks on the forum have always been there to help me out, so it’s nice to be able to do the same for others. Glad that revised solution looks like it’s going to work well for you. If we need to polish it up around the edges to get it working perfectly for your particular model, just let me know.
Here’s a tip that’s worked really well for me in situations like this one - when faced with a data structure that you don’t know how to handle in Power BI, like the Revenue Scaling Factor table, think “how can I take a wide and short structure (lots of columns, few rows) and make it narrow and long (few columns, lots of rows)?” DAX handles the latter beautifully, and the former quite poorly. Once we unpivoted the scaling factor table to make it a narrow and long structure, the DAX reduced down to a pretty basic CALCULATE/FILTER structure (but for the extra rigmarole with the matrix totals). Here’s another example where “narrow and long” made life much simpler:
It won’t solve all your problems, but I’ve been surprised how often it’s the key to cracking a difficult solution.
good tips indeed , thanks Brian! this whole exercise taught me a lot .
I have one question, because they now want to show the results of the spread revenue on the following period.
So if something is showing as say $9 million in P11 on the opportunities table , they want apply the scaling factor of P11
they now want to see the first iteration of results on P12
because is practically impossible to close any opportunity in the same month.
I honestly wish I knew this before asking you.
BTW: this is a mock up think I did adding another column to opportunity pipeline called Period+1
for example this is a $7M opty on P11 that the spread revenue should show starting in P12 (but using the scale factor for P11 of course).
sorry if this is messy …
Happy Easter if you celebrate it .
Stay safe !
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.
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.
eDNA Forum - Revenue Spread Solution3.pbix (118.1 KB)
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!