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 Pro...
  • 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.