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 ;

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's avatar
    ciken
    Icon for Resolver I rankResolver 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?