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

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