New logo (customers) count

[DianaSh]

Hi All,

I'm new to Power BI and I'm trying to solve the following problem: new customers are the ones that placed a new opportunity and haven't had any opportunities >0 in 3 prior years. I created a new 'Calendar for New Logo' table that has all the dates and I have  'Opportunity for new logo' table with all customers' opportunities.

So I calculated 3 measures:

1) Running Total = VAR MaxVisibleDate =

MAX ('Calendar for New Logo'[Date])-1

VAR Previous3YearDate = EDATE(MaxVisibleDate,-36)

VAR RESULT = CALCULATE(SUM('Opportunity for new logo'[Amount_USD for Closed and Commit]), DATESBETWEEN('Calendar for New Logo'[Date],Previous3YearDate, MaxVisibleDate))

RETURN RESULT

2) Opp_sum = VAR CurrentDate =

MAX('Calendar for New Logo'[Date])

VAR OppAmount =

CALCULATE (SUM ('Opportunity for new logo'[Amount_USD for Closed and Commit]), FILTER(ALL('Calendar for New Logo'), 'Calendar for New Logo'[Date] = CurrentDate))

RETURN OppAmount 

3) 

NewLogo =

IF(('Opportunity for new logo'[Opp_sum] > 0) && ('Opportunity for new logo'[Running Total]=0),1,0)

I see that the issue is somewhere with Opp_sum (I don't get numbers where there're supposed to be ones), and also in NewLogo I don't get total sum of all the "1"'s.

Please help me to understand how to correct my DAX.

Thanks in advance

[lbendlin]

This is a standard "penultimate entry"  pattern.

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[DianaSh]

I have 2 connected table that I use:

Opportunity for New Logo:

CloseDate	Opp_Account_Name__c	Q-Year	Amount_USD for Closed and Commit
Friday, September 30, 2022	Company	Q3-2022	0
Thursday, May 28, 2020	Customer1	Q2-2020	2800
Friday, May 29, 2020	Customer1	Q2-2020	34875
Tuesday, June 30, 2020	Customer2	Q2-2020	36549
Tuesday, July 21, 2020	Customer2	Q3-2020	2746

Calendar for New Logo:

Date

1/2/2018 0:00

1/3/2018 0:00

1/4/2018 0:00

1/5/2018 0:00

1/6/2018 0:00

1/7/2018 0:00

1/8/2018 0:00

1/9/2018 0:00

1/10/2018 0:00

 They are connected CloseDate <-> Date

[lbendlin]

You may not want to use connected tables for this scenario.  Your Calendar table looks incomplete - it needs to be on day level granularity and would need to extend to the current year.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[DianaSh]

Hi,

My Calendar covers all the period the issue. I added it so it includes each date in the period that I need. What can be incorrect here? 

I put sample data above, each row simply shows an opportunity with customer name, date and amount ($).

The problem is in code below. I expect it to calculate the sum of all the opportunities' amount per each date. Afterwards I visualize it in the table that shows sum of opportunities' amounts per each date per each customer.

Opp_sum =
VAR CurrentDate =
    MAX ( 'Calendar for New Logo'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Opportunity for new logo'[Amount_USD for Closed and Commit] ),
        FILTER (
            ALL ( 'Calendar for New Logo' ),
            'Calendar for New Logo'[Date] = CurrentDate
        )
    )

[lbendlin]

None of the close dates in your sample data is covered by your sample calendar.  Can't help you without better sample data.

[DianaSh]

Date

6/2/2020 0:00

6/1/2020 0:00

5/31/2020 0:00

5/30/2020 0:00

5/29/2020 0:00

5/28/2020 0:00

5/27/2020 0:00

5/26/2020 0:00

5/25/2020 0:00

5/24/2020 0:00

5/23/2020 0:00

5/22/2020 0:00

[DianaSh]

CloseDate	Opp_Account_Name__c	Amount_USD for Closed and Commit
Friday, May 29, 2020	Customer 1	1770
Friday, May 29, 2020	Customer 1	13200
Friday, May 29, 2020	Customer 2	4330.7
Friday, May 29, 2020	Customer 2	4.9
Friday, May 28, 2020	Customer 1	159.6
Friday, May 28, 2020	Customer 1	13200