Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sdmoody762
Frequent Visitor

First time using a slowly changing dimension to see what's happening to sales pipeline

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.

 

 

Screen Shot 2022-10-21 at 13.48.00.png

 

Opp NumberOpp StageForecast Date Forecast Amount Date Valid FromDate Valid ToIs Latest 
445566Lead10/14/22 $                        5,000.0010/1/2210/10/2022FALSE
445566Negotiation10/14/22 $                        5,000.0010/10/2210/13/2022FALSE
445566Quoted10/31/22 $                        4,982.3310/13/2212/31/9999TRUE
123432Quoted10/27/22 $                      10,000.0010/5/2212/31/9999TRUE
123432Lead9/28/22 $                        7,655.009/15/2210/5/2022FALSE
551212Lead10/29/22 $                      12,456.0010/5/2212/31/9999TRUE
777322Lead10/22/22 $                      10,000.0010/15/2210/19/2022FALSE
777322Lead10/28/22 $                      11,123.0010/19/2210/22/2022FALSE
777322Lead11/1/22 $                      14,400.0010/22/2212/31/9999TRUE
656889Lead10/15/22 $                      12,123.0010/14/2210/21/2022FALSE
656889Closed WON10/19/22 $                      12,123.0010/21/2212/31/9999TRUE
443228Lead10/18/22 $                        6,543.0010/14/2210/17/2022FALSE
443228Quoted10/21/22 $                        7,000.0010/17/2210/19/2022FALSE
443228Closed Lost10/21/22 $                        7,000.0010/20/2212/31/9999TRUE
1 ACCEPTED SOLUTION

ok so here is how I would shape your original data 

lbendlin_0-1667009824993.png

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:

 

lbendlin_1-1667010222885.png

 

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.

lbendlin_2-1667011586642.png

See attached

 

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

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 

lbendlin_0-1667009824993.png

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:

 

lbendlin_1-1667010222885.png

 

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.

lbendlin_2-1667011586642.png

See attached

 

Thank you VERY much!!!

Thank you for the challenge - I can use the learnings for my processes too.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.