Sales Pipeline: Filter multiple columns

[jpinciak]

Hello,

I have a pretty standard dataset from salesforce that I am using slicers to define the selection in my dashboard between a beginning and end pipeline date (from data below). Based on those selections I am looking to determine the total of the amount column that had a probability in the beginning pipeline but in the end pipeline is 0.

Example:

Start Pipeline - 4/1/2022

End Pipeline - 5/1/2022

Looking at the opportunity ID's determine if on 4/1/2022 probability > 0 and then 5/1/2022 = 0 and then taking the amount from 5/1/2022

Pipeline Date	Probability (%)	Opportunity ID	Amount
5/1/2022	50	0066e00001dT8M9	25,000
5/1/2022	25	0065A00001dSdNS	25,000
5/1/2022	25	0065A00001dRpzu	25,000
5/1/2022	0	0065A00001dQsJl	25,000
4/1/2022	25	0066e00001dT8M9	25,000
4/1/2022	25	0065A00001dSdNS	25,000
4/1/2022	25	0065A00001dRpzu	25,000
4/1/2022	25	0065A00001dQsJl	25,000

[jpinciak]

ave been using this to try to calculate the change between two dates and it doesn't appear to get the right number

SUMX(

VALUES(HistSalesPipeline[Opportunity ID])

,VAR vFirstDate =FIRSTDATE(HistSalesPipeline[Pipeline Date])

VAR vLastDate =LAstdate(HistSalesPipeline[Pipeline Date])

VAR vProbAtFirstDate =

CALCULATE (

max ( HistSalesPipeline[Unweighted ARR] ),

FILTER (

ALLSELECTED ( HistSalesPipeline ),

HistSalesPipeline[Pipeline Date] = vFirstDate && HistSalesPipeline[Opportunity ID]=earlier(HistSalesPipeline[Opportunity ID]) && HistSalesPipeline[StageStatus]="Open"

)

)

VAR vProbAtLastDate =

CALCULATE (

max ( HistSalesPipeline[Unweighted ARR] ),

FILTER (

ALLSELECTED ( HistSalesPipeline ),

HistSalesPipeline[Pipeline Date] = vLastDate && HistSalesPipeline[Opportunity ID]=earlier(HistSalesPipeline[Opportunity ID]) && HistSalesPipeline[StageStatus]="Open"

)

)

RETURN

IF(vProbAtFirstDate >0 && vProbAtLastDate >0 ,vProbAtLastDate- vProbAtFirstDate)

)

[Anonymous]

@jpinciak , did the measure that I posted on 30 June give you the correct results?

[Anonymous]

Hi  @jpinciak ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

New Amount =
VAR _selopp =
    SELECTEDVALUE ( 'Table'[Opportunity ID] )
VAR _startdate =
    CALCULATE (
        MIN ( 'Table'[Pipeline Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Opportunity ID] = _selopp )
    )
VAR _enddate =
    CALCULATE (
        MAX ( 'Table'[Pipeline Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Opportunity ID] = _selopp )
    )
VAR _mindaterate =
    CALCULATE (
        SUM ( 'Table'[Probability (%)] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Opportunity ID] = _selopp
                && 'Table'[Pipeline Date] = _startdate
        )
    )
VAR _maxdaterate =
    CALCULATE (
        SUM ( 'Table'[Probability (%)] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Opportunity ID] = _selopp
                && 'Table'[Pipeline Date] = _enddate
        )
    )
VAR _maxdateamt =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Opportunity ID] = _selopp
                && 'Table'[Pipeline Date] = _enddate
        )
    )
RETURN
    IF ( _mindaterate > 0 && _maxdaterate = 0, _maxdateamt, BLANK () )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[jpinciak]

bump

[jpinciak]

bump this bad boy up, I know one of you should know this easily

[jpinciak]

This was my attempt that didnt seem to work

Var currcalcdate = [CalcDate]

Var currcalcdate2 = [CalcDate2]

VAR currCalc =

Sumx( values(HistSalesPipeline[Opportunity ID]),

IF(and(CALCULATE(SUM(HistSalesPipeline[Probability (%)]),filter(HistSalesPipeline, HistSalesPipeline[Pipeline Date] = currcalcdate2)) = 0, CALCULATE(SUM(HistSalesPipeline[Probability (%)]),filter(HistSalesPipeline, HistSalesPipeline[Pipeline Date] = currcalcdate)) > 0),CALCULATE(SUM(HistSalesPipeline[Unweighted ARR]), filter(VALUES(HistSalesPipeline[Pipeline Date]), HistSalesPipeline[Pipeline Date]=currcalcdate)),BLANK()))

RETURN

currCalc

[Anonymous]

@jpinciak , this measure should work, and hopefully be easy to understand.

Amount of Opportunities that dropped to 0% in selected date range = 
    SUMX(
        VALUES('YourTable'[Opportunity ID])
        ,VAR vFirstDate = FIRSTDATE('YourTable'[PipelineDate])
         
        VAR vLastDate = LASTDATE('YourTable'[PipelineDate])
        
        VAR vProbAtFirstDate = 
            CALCULATE(
                MAX('YourTable'[Probability (%)])
                , 'YourTable'[Pipeline Date] = vFirstDate
            )
        
        VAR vProbAtLastDate =
            CALCULATE(
                MAX('YourTable'[Probability (%)])
                , 'YourTable'[Pipeline Date] = vLastDate
            )

        VAR vAmountAtLastDate = 
            CALCULATE(
                MAX('YourTable'[Amount])
                , 'YourTable'[Pipeline Date] = vLastDate
            )
 
       RETURN
            IF(vProbAtFirstDate > 100 && vProbAtLastDate = 0, vAmountAtLastDate)
    )