Hi.
First, SELECTEDVALUE is a function:
The function SELECTEDVALUE returns the value of the column reference passed as the first argument if it is the only value available in the filter context; otherwise, it returns blank or the default value passed as the second argument.
So, in my formula the use of it was deliberate. If it returned BLANK, it meant nothing was selected in the dimension or more than 1 value was selected.
Second, you have to have a calendar table in your model. Bear in mind that you should never, ever, ever expose your fact tables to the user. All slicing MUST BE done through dimensions. All columns in the fact table must be hidden. ALWAYS. No exceptions. Only measures can be made visible in the fact tables. YOU HAVE TO STICK TO THIS DESIGN if you want to have an easy time working with DAX and tabular models.
Your changed formula does not work because you're not doing it right.
So, let me recap what your model has to look like. First, you have to have a date table, marked as such in the model, called [Establishment Calendar] connected to Subscriptions (the fact table) via the Date field (1:*). There can't be subscriptions without an establishment date. Second, you have to have a dimension called [Cancellation Month] which will have 2 columns: one, hidden, will be an int storing numbers -1 (yes, -1), 0, 1, 2...., N, (call the column: MonthNumber) and the second (call it Month) will be a text column that will store the corresponding values: No Cancellation Month, 0th Month, 1st Month, 2nd Month, 3rd Month, 4th Month, 5th Month,..., Nth Month. Of course 'N' in the text is a number---the maximum number of cancellation month you want to consider. This one column will be exposed to the users. The first column, with integers, will be connected to Subscriptions (1:*) and hidden.
You should adjust your model to the above, not the other way round. This is a piece of good advice. Trust me.
Here's the measure you're after:
// This is a simple measure that just counts
// the number of subscriptions in the current
// context.
[Subscription Count] = COUNTROWS( Subscriptions )
// Probability that a subscription was
// cancelled in exactly the n'th month
// after subscription.
// 'visible' means: in the current context.
[Prob of Cancellation in Nth Month] =
// __maxDate is the last visible date in the
// 'Establishment Calendar' table.
var __maxDate = MAX( 'Establishment Calendar'[Date] )
var __month = SELECTEDVALUE( 'Cancellation Month'[MonthNumber] )
// This condition checks if one and only one value
// has been selected in the 'Cancellation Month'
// dimension and that it's not -1. -1 means
// No Cancellation Month.
var __shouldCalculate =
NOT ISBLANK( __month )
&& __month <> -1
return
if( __shouldCalculate,
var __establishUpperBound =
DATEADD( __maxDate, -__month, MONTH)
var __cancelled =
CALCULATE(
[Subscription Count] + 0, // this 0 must be here
'Establishment Calendar'[Date]
<= __establishUpperBound,
// This condition below makes sure
// that only subscriptions in the
// current context are filtered by the
// above condition. If you want to
// consider all subscriptions before
// the upper bound disregarding the selection
// in the calendar, then remove this
// condition below.
VALUES( 'Establishment Calendar'[Date] )
)
var __all =
CALCULATE(
[Subscription Count],
'Establishment Calendar'[Date]
<= __establishUpperBound,
// Same remark applies to this VALUES
// condition as above.
VALUES( 'Establishment Calendar'[Date] ),
ALL( 'Cancellation Month' )
)
return
divide( __cancelled, __all )
)
This should calculate what you need... Well, almost. If you need to have the probability that cancellation will happen in no more than N months, then you'll have to do this:
Pr(Cancellation happens in no more than N months) = Summation of Pr(Cancellation happens in k-th month) over k=0,1,2,...,N.
This can be coded as:
[Prob Cancellation happens before or in Nth month] =
var __month = SELECTEDVALUE( 'Cancellation Month'[MonthNumber] )
var __shouldCalculate =
NOT ISBLANK( __month )
&& __month <> -1
var __monthsToIterate =
FILTER(
ALL( 'Cancellation Month' ),
AND(
'Cancellation Month'[MonthNumber] <= __month,
'Cancellation Month'[MonthNumber] > -1
)
)
var __probs =
addcolumns(
__monthsToIterate,
"Prob", [Prod of Cancellation in Nth Month]
)
var __allProbsExist =
ISEMPTY(
FILTER(
__probs,
ISBLANK([Prob])
)
)
var __cumulativeProb = sumx( __probs, [Prob] )
return
if( __shouldCalculate && _allProbsExist,
__cumulativeProb
)
Of course, bear in mind that the probs are only ESTIMATES and it's possible that the sum might at times be above 1. This is because the universe for each k is different. We could make it the same but that would reduce the number of cases and hence the quality of the estimates of almost all the constituent probabilities.
Hint: If you get a value > 1, then add an IF condition to the above measure to cap it at 1.
Let me know how it goes.
Best
D
