Help with What if Parameter and DateDiff

[ciken]

Hi community!

I am trying to write a what if parameter that would change my retention based on the input value. I have created a calculated column that is listed below. Right now, it looks to see if the difference between last purchase date and today is more than 120 days consider the customer LOST and if not, consider them Retained. 

Retained vs Lost =

VAR LastOrder = 'Lead'[Last Time Purchase]

VAR Datedifference = DATEDIFF(LastOrder,Today(),DAY)

RETURN

IF(ISBLANK(Datedifference),"Lost",IF(Datedifference>120,"Lost","Retained"))


What I'm trying to accomplish is the ability to change the "120" in the RETURN line to be a what if parameter so that a rep can change their retention to be more or less than the defined field. But, every time I write the parameter and change the last line ot IF(DateDifference>[Parameter], "Lost", Retained") Everything turned out "Retained. It does not take into account the parameter at all.  

Since the field isn't a measurement but a calculated column, I cannot figure out how to make the parameter work. Any advice is appreciated!!

TIA!

[v-yalanwu-msft]

Hi ， @ciken ；

You could modify it.

Lost count = 
IF (
    ISINSCOPE ( 'Table'[Column1] ),
   CALCULATE(COUNT([date]),FILTER(ALLEXCEPT('Lead','Lead'[index]),[Retained vs Lost]=MAX('Table'[Column1]))),
    COUNT ( 'Lead'[date] ))

Lost count% = 
var _count=CALCULATE( COUNT ( 'Lead'[date] ),ALLEXCEPT('Lead','Lead'[index]))
return 
IF (
    ISINSCOPE ( 'Table'[Column1] ),
  [Lost count]/_count,
    1)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @ciken ;

If you want to do dynamic parameter values such as 120, you have to use measure. If you want to continue counting, you have to create measure. Here is one way:
1. Manually fill in a table.

2.create a measure to calculate count.

Lost count = 
IF (
    ISINSCOPE ( 'Table'[Column1] ),
    SWITCH (
        MAX ( 'Table'[Column1] ),
        "Lost", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ),
        "Retained", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] )),
    COUNT ( 'Lead'[date] ))

3. create a measure to calculate percent of count.

Lost count% = 
var _count= COUNT ( 'Lead'[date] )
return 
IF (
    ISINSCOPE ( 'Table'[Column1] ),
    SWITCH (
        MAX ( 'Table'[Column1] ),
        "Lost",DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ),_count),
        "Retained", DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] ),_count)),
    1)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ciken]

Thank you for these measures but they did not work. When I took your pbix and included a second Index letter it doesn't change the lost vs retained except for the total count (see picture). Something is still broken that it's not breaking them out uniquely but rather still counting the Lost vs Retained field no matter the index. Since I'm trying to get to a distinct count by account name I need each index (account name) to count distinct. As you can see the percentages are going from total against retained / lost but the numbers themselves did not change. 

[v-yalanwu-msft]

Hi, @ciken ;

Try to modify it(ps:there is a measure).

Retained vs Lost = 
VAR LastOrder = MAX('Lead'[date])
VAR Datedifference =
    DATEDIFF ( LastOrder, TODAY (), DAY )
RETURN
    IF (
        ISBLANK ( Datedifference ),
        "Lost",
        IF ( Datedifference > [Parameter Value], "Lost", "Retained" )
    )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ciken]

This helped to create the measure (thank you!!). But I'm looking to have a little bit different picture by using the measure to build retained and lost columns. Is there a way to take this measure and create a count of retained vs lost as you can see below?

[ciken]

Hi @amitchandak , Thank you but, this provided an error message: 

"A single value for column 'Last Time Purchase' in table 'Lead' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifiying an aggregation such as Min, max, count or sum to get a single result"

I tried to use the actual calculation in my 'Lead' table 

First Time Purchase = CALCULATE(MIN('Order'[EffectiveDate]),FILTER(ALL('Order'),'Order'[Consumer_Lead__c]=EARLIER('Lead'[Id])),FILTER('Order','Order'[Status]<>"Voided"))

But that led to another error: 
Earlier /Earliest refers to an  earlier row context which does not exist

Thoughts?

[amitchandak]

@ciken , No you can not use slicer value in a calculated column, you have to create a measure

like

Retained vs Lost =

VAR LastOrder = 'Lead'[Last Time Purchase]

VAR Datedifference = DATEDIFF(LastOrder,Today(),DAY)

RETURN

Sumx( 'Lead', IF(ISBLANK(Datedifference),"Lost",IF(Datedifference>120,"Lost","Retained")))

In this measure you should be able to use whatif in place of 120