Forum Discussion

helpmeplease123's avatar
helpmeplease123
Frequent Visitor
4 years ago

Sales Pipeline: Change Between Pipeline Dates - Filter multiple columns

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 a different calculations

 

1. Increase in amount from one date to another (decrease as well)

2. Opps that moved from <100 probability to closed won, a factor here is that the opportunity needs to existing in the beginning date pipeline selection

3. Newly created opportunities where it didnt exist in beginning pipline and in end date pipline isnt probability 0 or 100

 

 

 

 

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 DateProbability (%)StageOpportunity IDAmount
5/1/202210Closed Won0066e00001dT8M9       25,000
5/1/202225Identifying0065A00001dSdNS       25,000
5/1/202225Identifying0065A00001dRpzu       25,000
5/1/20220Closed Lost0065A00001dQsJl       25,000
4/1/202225Identifying0066e00001dT8M9       25,000
4/1/202225Identifying0065A00001dSdNS       25,000
4/1/202225Identifying0065A00001dRpzu       25,000
4/1/202225Identifying0065A00001dQsJl       25,000
Need Help
Tips and Tricks

4 Replies

  • helpmeplease123's avatar
    helpmeplease123
    Frequent Visitor
    4 years ago

    I have been using this to reflect the change between pipelines but it doesn't seem to work correctly

     

    Waterfall Change =

     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)
        )