Forum Discussion

ciken's avatar
ciken
Icon for Resolver I rankResolver 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...
Need Help
  • v-yalanwu-msft's avatar
    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity 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.