Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

IF Statements

Hi Guys

 

Trying to count the potential revenue from opportunities that have not been closed yet i.e. Not achieved contract signed stage AND that have not gone past their expected gain date. Idea is to give an insight into the potential revenue we have in the pipeline.

 

Im assuming an IF statmenet would be the best approach?

 

Cheers!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    I test with this calculated column

    Column =
CALCULATE (
    SUM ( Sheet1[revenue ] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[name] ),
        [date] <= [contract signed stage]
            && [date] <= [expected gain date]
    )
)

    SUM(Sheet1[revenue ])->”count the potential revenue”

    FILTER(ALLEXCEPT(Sheet1,Sheet1[name]),[date]<=[contract signed stage]&&[date]<=[expected gain date])-> “Not achieved contract signed stage AND that have not gone past their expected gain date”

    Anyway, I only use a simple example to illustrate how to achieve your requirement. How the formula should be written depends on your dataset and condition.

     

     

    Best regards

    Maggie

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I think , something like this will solve your need.

     

    CALCULATE ( COUNT( OPPORTUNITIES), FILTER ( TABLE1,TABLE1[STAGE]= "Contract Signed") ||FILTER ( TABLE1,TABLE1[STAGE]= "Not Acheived ")

     

    Thanks

    Raj

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Anonymous

    I test with this calculated column

    Column =
CALCULATE (
    SUM ( Sheet1[revenue ] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[name] ),
        [date] <= [contract signed stage]
            && [date] <= [expected gain date]
    )
)

    SUM(Sheet1[revenue ])->”count the potential revenue”

    FILTER(ALLEXCEPT(Sheet1,Sheet1[name]),[date]<=[contract signed stage]&&[date]<=[expected gain date])-> “Not achieved contract signed stage AND that have not gone past their expected gain date”

    Anyway, I only use a simple example to illustrate how to achieve your requirement. How the formula should be written depends on your dataset and condition.

     

     

    Best regards

    Maggie