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)RETURNIF(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!

Resolver I

4 years ago

Solved

Help with What if Parameter and DateDiff

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!

Need Help

v-yalanwu-msft

4 years ago

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.

Help with What if Parameter and DateDiff.pbix59 KB

7 Replies

amitchandak
Super User
4 years ago
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
ciken
Resolver I
4 years ago
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?
v-yalanwu-msft
Community Support
4 years ago
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.
datediff.pbix57 KB
- ciken
  Resolver I
  4 years ago
  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?
v-yalanwu-msft
Community Support
4 years ago
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.
Help with What if Parameter and DateDiff.pbix59 KB
- ciken
  Resolver I
  4 years ago
  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

Community Support

4 years ago

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.

Help with What if Parameter and DateDiff.pbix60 KB

Forum Discussion

Help with What if Parameter and DateDiff

7 Replies

Related content

DATEDIFF DAX

Parameter in summarize

notebook parameter

DATEDIFF Working Days

Parameters to fabric pipeline