Forum Discussion
DAX Help
- 7 years ago
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 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
- amcmackin7 years agoFrequent Visitor
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-msft7 years agoMicrosoft Employee
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
- amcmackin7 years agoFrequent Visitor
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.
Account Product Stage Close Date Most Recent Close Date 1234ABCD A Sale Won 1/1/2015 1/1/2017 1234ABCD A Sale Lost 1/1/2016 1/1/2017 1234ABCD A Sale Won 1/1/2017 1/1/2017 1234ABCD B Sale Won 1/1/2015 1/1/2015 1234ABCD B Renewal 1/1/2016 1234ABCD B Qualified 1/1/2017 WXYZ5678 A Sale Won 1/1/2015 1/1/2015 WXYZ5678 B Sale Won 1/1/2015 1/1/2016 WXYZ5678 B Sale Lost 1/1/2016 1/1/2016 WXYZ5678 C Renewal 1/1/2015 WXYZ5678 C Sale Won 1/1/2016 1/1/2016 WXYZ5678 C Qualified 1/1/2017 Please let me know if this is still unclear and I can try to elaborate some more.
Thanks,
Andrew