Forum Discussion

amcmackin's avatar
amcmackin
Frequent Visitor
7 years ago
Solved

DAX Help

I am trying to calculate the most recent close date for Salesforce opportunities that are grouped first by account number and then by the product that the client is using. However, I need to do this only for items tagged as "Sale Won" or "Sale Lost". I am using the below formula which seems to work most of the time but not all of the time.

 

MR Stage Date = if(or(Opportunity[Stage]= "Sale Won",Opportunity[Stage]= "Sale Lost"),
CALCULATE(MAX(Opportunity[Close Date].[Date]),FILTER(Opportunity,Opportunity[Account ID]=EARLIER(Opportunity[Account ID])),FILTER(Opportunity,Opportunity[Product]=EARLIER(Opportunity[Product])),FILTER(Opportunity,or(Opportunity[Stage]= "Sale Won",Opportunity[Stage]= "Sale Lost"))),BLANK())

 

Here is a sample of the results that should be produced

 

Client IDProductStageClose DateMost Recent Close Date
1234ABCDASale Won1/1/20161/1/2018
1234ABCDASale Won1/1/20171/1/2018
1234ABCDASale Won1/1/20181/1/2018
1234ABCDBSale Lost1/1/20161/1/2017
1234ABCDBSale Lost1/1/20171/1/2017
1234ABCDBRenewal1/1/2018 

 

Any help here is greatly appreciated!

 

Thanks

  • Vvelarde's avatar
    Vvelarde
    7 years ago

    amcmackin

     

    Hi, try with this calculated column:

     

    Column = 
IF (
    Table1[Stage] IN { "Sale Won"; "Sale Lost" },
    CALCULATE (
        LASTDATE ( Table1[Close Date] ),
        FILTER (
            Table1,
            Table1[Account] = EARLIER ( Table1[Account] )
                && Table1[Product] = EARLIER ( Table1[Product] )
                && Table1[Stage] IN { "Sale Won"; "Sale Lost" }
        )
    )
)

     

    Or a measure:

     

    Measure =
VAR Account =
    SELECTEDVALUE ( Table1[Account] )
VAR Product =
    SELECTEDVALUE ( Table1[Product] )
RETURN
    IF (
        SELECTEDVALUE ( Table1[Stage] ) IN { "Sale Won"; "Sale Lost" },
        CALCULATE (
            LASTDATE ( Table1[Close Date] ),
            FILTER (
                Table1,
                Table1[Account] = Account
                    && Table1[Product] = Product
                    && Table1[Stage] IN { "Sale Won"; "Sale Lost" }
            )
        )
    )

     

    Regards

     

    Victor

8 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi amcmackin,

    Based on my test, you could refer to below formula:

    MR Date = IF('Opportunity'[Stage]="Sale Won"||Opportunity[Stage]="Sale Lost",CALCULATE(MAX('Opportunity'[Close Date]),FILTER('Opportunity','Opportunity'[Product]=EARLIER(Opportunity[Product])&&'Opportunity'[Stage]="Sale Won"||Opportunity[Stage]="Sale Lost")))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

    • amcmackin's avatar
      amcmackin
      Frequent Visitor
      7 years ago

      Hi v-danhe-msft,

       

      Thanks for your help here. There still seems to be an issue here. I have filtered my data to show one account and the data is pulling in a date that isnt in this account. Below are the results that I am getting. All items are either sale won or sale lost and the duplicate dates in the close date feild a

       

      Close Date

      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Thursday, September 29, 2016
      Thursday, September 29, 2016
      Tuesday, September 29, 2015
      Tuesday, September 29, 2015
      Tuesday, September 29, 2015

       

      MR Date

      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM
      8/14/2019 12:00:00 AM

      Expected Result

      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Thursday, August 31, 2017
      Tuesday, September 29, 2015

       

      *Bottom is a new product

       

      Hopefully this helps clairify the issue im having.

       

      Thanks,

      Andrew

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi amcmackin,

        I could not understand what you want, if the [State] is either 'Sale Won' nor 'Sale Lost', what do you want to show? Could you please post your desired result like the picture you have posted before?

         

        Regards,

        Daniel He

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi amcmackin,

    Why not use a slicer for Opportunity[Stage] in the report and select both "Sale Won" and  "Sale Lost". It gives more clarity to the users as well when they select MR Stage Date, they know that they are only looking for Sales won / lost and not all categories.

    • amcmackin's avatar
      amcmackin
      Frequent Visitor
      7 years ago

      Hi Anonymous,

       

      So I am actually using this field to help calculate another field. I am interested in tracking ARR, but only the most recent value and if the most recent value is sale lost make it 0. I was using the MR date to find the date and then say if MR Date = Close Date for that record, pull that ARR. 

       

      Thanks,

      Andrew