Forum Discussion
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 ...
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
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
