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

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