cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amcmackin
Frequent Visitor

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 only for items tagged as "Sale Won" or "Sale Lost". I am using the below formula which seems to work most of the time but not all of the time.

 

MR Stage Date = if(or(Opportunity[Stage]= "Sale Won",Opportunity[Stage]= "Sale Lost"),
CALCULATE(MAX(Opportunity[Close Date].[Date]),FILTER(Opportunity,Opportunity[Account ID]=EARLIER(Opportunity[Account ID])),FILTER(Opportunity,Opportunity[Product]=EARLIER(Opportunity[Product])),FILTER(Opportunity,or(Opportunity[Stage]= "Sale Won",Opportunity[Stage]= "Sale Lost"))),BLANK())

 

Here is a sample of the results that should be produced

 

Client IDProductStageClose DateMost Recent Close Date
1234ABCDASale Won1/1/20161/1/2018
1234ABCDASale Won1/1/20171/1/2018
1234ABCDASale Won1/1/20181/1/2018
1234ABCDBSale Lost1/1/20161/1/2017
1234ABCDBSale Lost1/1/20171/1/2017
1234ABCDBRenewal1/1/2018 

 

Any help here is greatly appreciated!

 

Thanks

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

View solution in original post

8 REPLIES 8
diwakarjha
Frequent Visitor

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.

Hi @diwakarjha,

 

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 

v-danhe-msft
Microsoft
Microsoft

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:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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?

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Community Champion
Community Champion

@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




Lima - Peru

View solution in original post

Hi @Vvelarde

 

That worked. Thanks for your help everyone.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors