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 ...
  • 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

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

  • amcmackin's avatar
    amcmackin
    Frequent Visitor
    7 years ago

    Hi v-danhe-msft

     

    For each Account, then for each Product within that Account find the most recent close date that has either the stage Sale Won or Sale Lost. If the stage is not Sale Won or Lost, make it blank. I have expanded the example from before to hopeful provide some more claification.

     

    AccountProductStageClose DateMost Recent Close Date
    1234ABCDASale Won1/1/20151/1/2017
    1234ABCDASale Lost1/1/20161/1/2017
    1234ABCDASale Won1/1/20171/1/2017
    1234ABCDBSale Won1/1/20151/1/2015
    1234ABCDBRenewal1/1/2016 
    1234ABCDBQualified 1/1/2017 
    WXYZ5678ASale Won1/1/20151/1/2015
    WXYZ5678BSale Won1/1/20151/1/2016
    WXYZ5678BSale Lost1/1/20161/1/2016
    WXYZ5678CRenewal1/1/2015 
    WXYZ5678CSale Won1/1/20161/1/2016
    WXYZ5678CQualified 1/1/2017 

     

    Please let me know if this is still unclear and I can try to elaborate some more.

     

    Thanks,

    Andrew

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      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

      • amcmackin's avatar
        amcmackin
        Frequent Visitor
        7 years ago

        Hi Vvelarde

         

        That worked. Thanks for your help everyone.