Forum Discussion

mroberts_troy's avatar
mroberts_troy
Advocate II
4 years ago
Solved

Standard S-Curve Measure

I'm trying to create a standar S-Curve model, and am having zero luck.  I'm trying to use information on the internet (like this: Some Basics on the Value of S Curves and Market Adoption of a New Product - Supply Chain Link Blog - Arkieva) but so far, I'm only generating straight lines.  Can someone help with the formula?  

 

The information I have is:

  • How many widgets need to be created
  • Start date of creating widgets
  • End date of creating widgets

Thanks!

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Cracked it! Exciting DAX fun to be honest which needs some explanation as to why the stuff I sent worked in isolation but not in your solution.

     

    Your date table is related to a number of fact tables which contain blank dates. In order to deal with that in a strong relationship DAX introduces a blank row into your date table behind the scenes. (Blank row in DAX - SQLBI)

    When the date slicer is present and has a selection made the blank row is filtered out.

    When the date slicer is present but it's set at the lowest value the blank row remains.

     

    The EXP function is basically saying it doesn't know how to calculate e^BLANK which is fair enough!

     

    First solution was to put in a page filter to remove the blank row:

     

    Better solution is update the DAX to deal with the situation:

    CM_ROW_Tax Card Baseline S-Curve = 
VAR k = .18
VAR a = .43
VAR MinVal = 0
VAR MaxVal = 10000
VAR StartDate = CALCULATE( MIN ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] ))
VAR EndDate =  CALCULATE( MAX ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] ))

VAR CurrentDate = SELECTEDVALUE( dimCalendar[Date] )
VAR XVal = CurrentDate - StartDate

VAR MaxXVal = EndDate - StartDate
VAR MeanXVal = MaxXVal/2

VAR Result = 
IF(
    NOT ISBLANK(CurrentDate),
    MinVal + (MaxVal - MinVal) * DIVIDE ( 1, (1 + EXP(-1*k*( XVal- MeanXVal )))^a ) 
)

RETURN 
   Result


    In terms of the impact of the parameters I'd just based it on the formula in the blog you sent. I'll have another read and ponder on it.

9 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Hi,
    I created some paramaters in power bi  for a, k and number of widgets (you could hard code or calculate from other measures) if you know what they are:

    I also created a basic date table:

    Date = 
    CALENDAR(
        DATE ( 2021, 01, 01 ),
        DATE ( 2021, 12, 31 )
    )

     
    Measure for standard S curve:

    Standard S Curve = 
VAR k = [k Value]
VAR a = [a Value]
VAR MinVal = 0
VAR MaxVal = [#Widget Value]
VAR StartDate = CALCULATE( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR EndDate =  CALCULATE( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )

VAR XVal = 
    SELECTEDVALUE( 'Date'[Date] ) - StartDate

VAR MaxXVal = EndDate - StartDate
VAR MeanXVal = MaxXVal/2

VAR Result = 
    MinVal + (MaxVal - MinVal) * DIVIDE ( 1, (1 + EXP(-1*k*( XVal- MeanXVal )))^a ) 

RETURN 
   Result

     

    Date[Date] goes on x axis and the slicer:

    I suspect from your description you may have missed the ALLSELECTED?

     

    You can see my solution in this: Demo File

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi mroberts_troy 

    The information I have is:

How many widgets need to be created
Start date of creating widgets
End date of creating widgets

    Could you provide a sample file or some sample data? And please provide your desired outcome based on your sample. I'll try to solve it. Thanks.

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • mroberts_troy's avatar
    mroberts_troy
    Advocate II
    4 years ago

    v-xiaotang thanks for the response, but honestly, a formula for a theoretical S-Curve shouldn't need any "data".  The main thing you would need to know is number of widgets to start (zero), number of widgets to finish (can be any number), and how steep the curve should be (variable).  So I'm hoping someone has a formula that will render that curve, and then I can just plug in my measures as appropriate.

  • mroberts_troy's avatar
    mroberts_troy
    Advocate II
    4 years ago

    bcdobbs in your file and explanation, it looks perfect.  However when I copy in your formula, I get an error on the visual and it says "An argument of function 'EXP' has the wrong data type of the result is too large or too small".  I'm attaching my file for you to see.
    Data File 

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      Hi,
      I can make your file work by marking the date dimension as a date table and then putting a slicer on to restrict the date range.

      I'm perplexed as it works up to the point where 1st Jan is in the filter context! Investigating now

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Cracked it! Exciting DAX fun to be honest which needs some explanation as to why the stuff I sent worked in isolation but not in your solution.

         

        Your date table is related to a number of fact tables which contain blank dates. In order to deal with that in a strong relationship DAX introduces a blank row into your date table behind the scenes. (Blank row in DAX - SQLBI)

        When the date slicer is present and has a selection made the blank row is filtered out.

        When the date slicer is present but it's set at the lowest value the blank row remains.

         

        The EXP function is basically saying it doesn't know how to calculate e^BLANK which is fair enough!

         

        First solution was to put in a page filter to remove the blank row:

         

        Better solution is update the DAX to deal with the situation:

        CM_ROW_Tax Card Baseline S-Curve = 
VAR k = .18
VAR a = .43
VAR MinVal = 0
VAR MaxVal = 10000
VAR StartDate = CALCULATE( MIN ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] ))
VAR EndDate =  CALCULATE( MAX ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] ))

VAR CurrentDate = SELECTEDVALUE( dimCalendar[Date] )
VAR XVal = CurrentDate - StartDate

VAR MaxXVal = EndDate - StartDate
VAR MeanXVal = MaxXVal/2

VAR Result = 
IF(
    NOT ISBLANK(CurrentDate),
    MinVal + (MaxVal - MinVal) * DIVIDE ( 1, (1 + EXP(-1*k*( XVal- MeanXVal )))^a ) 
)

RETURN 
   Result


        In terms of the impact of the parameters I'd just based it on the formula in the blog you sent. I'll have another read and ponder on it.

  • mroberts_troy's avatar
    mroberts_troy
    Advocate II
    4 years ago

    bcdobbs Also, I just noticed, your "A" slider doesn't seem to have very much impact at all on the top curve.  I think there would be 3 variables, right?

    • Slope of bottom curve
    • Slope of top curve
    • Angle of incline

    Or am I missing something?  Thanks!

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      I think this may be my interpretation of:

      The model I provided lets you change a and k. However I interpreted x0 as the half way point on the x axis ("mean of time buckets").

      Instead of 

       

      VAR MeanXVal = MaxXVal/2

       

       

      Try

       

      VAR MeanXVal = 50

       

      and adjust that value (You could rename to Xzero in the DAX if you wanted!)