Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good day everyone,
This is the first time I've been in receipt of a type II slowly changing dimension (SDC), and I'm eager to help our sales leadership better understand what is happening within the sales pipeline of open opportunities. I'm having trouble identifying the best path to take to get the time filtering correct on my dax though, and was hoping somebody has done this before or has an example to be referenced to ensure accuracy and max performance efficiency.
Effectively I want to create a matrix visual with some measures in it that show how much was active pipeline this week and how much was last week, and then create detail measures of what was in the delta between the two weeks {What closed won, what closed lost, what was pushed into the future for a forecast date}. I hope this makes sense. Below is an example of what I'm looking to do visually and an example of my SDC that I'm struggling to get started with for any of the measures. Thank you in advance for any insight that can be provided.
Opp Number | Opp Stage | Forecast Date | Forecast Amount | Date Valid From | Date Valid To | Is Latest |
445566 | Lead | 10/14/22 | $ 5,000.00 | 10/1/22 | 10/10/2022 | FALSE |
445566 | Negotiation | 10/14/22 | $ 5,000.00 | 10/10/22 | 10/13/2022 | FALSE |
445566 | Quoted | 10/31/22 | $ 4,982.33 | 10/13/22 | 12/31/9999 | TRUE |
123432 | Quoted | 10/27/22 | $ 10,000.00 | 10/5/22 | 12/31/9999 | TRUE |
123432 | Lead | 9/28/22 | $ 7,655.00 | 9/15/22 | 10/5/2022 | FALSE |
551212 | Lead | 10/29/22 | $ 12,456.00 | 10/5/22 | 12/31/9999 | TRUE |
777322 | Lead | 10/22/22 | $ 10,000.00 | 10/15/22 | 10/19/2022 | FALSE |
777322 | Lead | 10/28/22 | $ 11,123.00 | 10/19/22 | 10/22/2022 | FALSE |
777322 | Lead | 11/1/22 | $ 14,400.00 | 10/22/22 | 12/31/9999 | TRUE |
656889 | Lead | 10/15/22 | $ 12,123.00 | 10/14/22 | 10/21/2022 | FALSE |
656889 | Closed WON | 10/19/22 | $ 12,123.00 | 10/21/22 | 12/31/9999 | TRUE |
443228 | Lead | 10/18/22 | $ 6,543.00 | 10/14/22 | 10/17/2022 | FALSE |
443228 | Quoted | 10/21/22 | $ 7,000.00 | 10/17/22 | 10/19/2022 | FALSE |
443228 | Closed Lost | 10/21/22 | $ 7,000.00 | 10/20/22 | 12/31/9999 | TRUE |
Solved! Go to Solution.
ok so here is how I would shape your original data
The "Date valid to" and "Latest" columns don't really add value. This also gets rid of the silly 12/31/9999 date that is really bad for the time intelligence functions.
Next will be the list of saturdays:
And the last thing to do is to compute the status of each opportunity for each of the snapshots. The opportunity may not yet exist at a certain snapshot date, or it may have a "Date valid From" before or on the snapshot date. You can combine that in one logic - "get the max "Date valid From" that is on or before the snapshot date and then retrieve the value (for example the Opp Stage) for that date.
See attached
I would strongly recommend you look into using the OpportunityFieldHistory object. That will allow you to pivot from treating this as a SCD to treating it as what it is - event based reporting. You can still compute your weekly snapshots if you want but that would be a little step back.
Thank you for the recommendation however the SDC is currently what I have and is the system of record for other views already in production and what I've been asked to use by the executive sponsor. I've got to believe I can still get to the outcome we're after with the SDC, it's the maturity of the DAX expression that is eluding me a bit. Specifically, dynamically selecting the correct rows based on if the row in the SDC was active in the selected week, and using the most recent one should there be a situation of many rows entered for that week.
Any reference to where this, or something close to it has been solved is greatly appreciated. I struggle to believe I'm the first to get tied up on something of this nature.
I see this a lot (people who are incredulous that they are sitting on the bleeding edge) .
Can you clearly define what you mean by "this week" and "previous week" ? Are we talking calendar weeks or fiscal weeks? Weeks in a year or weeks in a quarter? Begin of week or end of week? which day of the week?
I bet.
I am specifically after CALENDAR WEEK, with WEEK ending on SATURDAY. It may be worth noting that I have my DATE TABLE already configured for CALENDAR WEEK, and WEEK START/END, WEEK n YEAR to support this as well.
ok so here is how I would shape your original data
The "Date valid to" and "Latest" columns don't really add value. This also gets rid of the silly 12/31/9999 date that is really bad for the time intelligence functions.
Next will be the list of saturdays:
And the last thing to do is to compute the status of each opportunity for each of the snapshots. The opportunity may not yet exist at a certain snapshot date, or it may have a "Date valid From" before or on the snapshot date. You can combine that in one logic - "get the max "Date valid From" that is on or before the snapshot date and then retrieve the value (for example the Opp Stage) for that date.
See attached
Thank you VERY much!!!
Thank you for the challenge - I can use the learnings for my processes too.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |