Opportunity created in current month

[Anonymous]

Dear All,

I've the below sample data set as shown below. This fact table linked to Date dimension based on closeddate.

I need to calcuate the belwo metrics.

New opportunity created = sum of the quanity for that particular month and isclose should be false, need to consider created date. 

marketing generated - sum of total qty for each month where mareketing generated is true

closed - sum of qty where isclosed is true

Opp_ID	CLOSEDATE	ISCLOSED	CREATEDDATE	QUANTITY	MARKETING_GENERATED
0062p00000z9ieLAAQ	8/26/2019	TRUE	7/23/2019	86	FALSE
0062p00000z9imAAAQ	8/13/2019	TRUE	7/23/2019	40	FALSE
0062p000010NS6bAAG	11/22/2019	FALSE	7/31/2019	44	TRUE
0062p000010NV8cAAG	8/12/2019	TRUE	8/2/2019	32	FALSE
0062p000010NZPYAA4	9/2/2019	TRUE	8/6/2019	16	FALSE
0062p000010O9wGAAS	10/9/2019	TRUE	8/21/2019	NULL	TRUE
0062p000010OLzoAAG	10/5/2019	TRUE	8/26/2019	21	FALSE
0062p000010OON8AAO	11/29/2019	FALSE	8/27/2019	59	TRUE
0062p000010OUvPAAW	9/2/2019	TRUE	8/30/2019	NULL	FALSE
0062p000010OdesAAC	9/13/2019	TRUE	9/4/2019	46	FALSE
0062p000010OhAcAAK	3/13/2020	FALSE	9/6/2019	43	TRUE
0062p000010OhRsAAK	12/20/2019	FALSE	9/6/2019	20	TRUE
0062p000010OpN7AAK	9/18/2019	TRUE	9/12/2019	NULL	TRUE
0062p000010fDUGAA2	9/16/2019	TRUE	9/13/2019	5	FALSE
0062p000010fJJ7AAM	12/27/2019	FALSE	9/17/2019	41	TRUE
0062p000010fJFZAA2	5/15/2020	FALSE	9/17/2019	26	FALSE
0062p000010fQctAAE	2/14/2020	FALSE	9/20/2019	NULL	FALSE
0062p000010fTmUAAU	9/26/2019	TRUE	9/23/2019	4	TRUE
0062p000010fWKrAAM	11/15/2019	FALSE	9/24/2019	18	TRUE
0062p000010fYUWAA2	9/30/2019	TRUE	9/25/2019	NULL	TRUE
0062p000010fmNdAAI	11/22/2019	FALSE	10/1/2019	12	FALSE
0062p000010fmMQAAY	11/22/2019	FALSE	10/1/2019	44	FALSE
0062p000010fwldAAA	10/9/2019	TRUE	10/4/2019	5	FALSE
0062p000010fxA8AAI	11/22/2019	FALSE	10/4/2019	14	FALSE
0062p000010fzPfAAI	10/25/2019	TRUE	10/5/2019	21	TRUE
0062p000010fzPGAAY	11/15/2019	FALSE	10/5/2019	54	FALSE
0062p000010gHdxAAE	10/17/2019	TRUE	10/11/2019	18	FALSE
0062p000010gJnmAAE	11/15/2019	FALSE	10/13/2019	NULL	FALSE
0062p000010gY5kAAE	12/20/2019	FALSE	10/20/2019	NULL	FALSE
0062p000010gY5pAAE	11/29/2019	FALSE	10/20/2019	NULL	FALSE
0062p000010gaM3AAI	1/17/2020	FALSE	10/21/2019	NULL	FALSE

Output should look like below;

Month	Jan	Feb	Mar
New opportunites created
Marketing generated
closed
Total Qty

Thanks for the support.

[Washivale]

Hi @Anonymous ,

You got a good use case to use "USERELATIONSHIP" function of dax in this case.

Create an inactive relationship between createddate and Date dimension.

then for this (New opportunity created = sum of the quanity for that particular month and isclose should be false, need to consider created date.) create a measure as below:

New Opportunity Created = Calculate(SUM(Quantity), isClose = false, USERELATIONSHIP(createddate, Date[Date]))

for marketing generated = calculate (SUM(Quantity), [marketing generated] = true) 

for closed if you are looking to get closed values based on closed date , you can create measure as below:

closed = Calculate(SUM(QUANTITY), isclosed = true) should do the trick

let me know if it helps

Regards,

Washivale

[Anonymous]

Hi, I tried to use the below expression but nothig is showing the table.

New Opportunities Created = CALCULATE(sum(Opportunities[TOTALOPPORTUNITYQUANTITY]),Opportunities[ISCLOSED]="false",USERELATIONSHIP(Opportunities[CREATEDDATE],'Calendar'[Date]))

[Washivale]

Hi @Anonymous ,

let me know from which table you have selected date column in matrix to see the results, also let me know format for created date column and isclosed column

Regards,

washivale

[PattemManohar]

@Anonymous Please follow the below steps:

1. Create a new column as Type as below

Type = 
VAR _NewOpportunity = IF(Test10[ISCLOSED]=FALSE(),"Y","N")
VAR _Marketing = IF(Test10[MARKETING_GENERATED]=TRUE(),"Y","N")
VAR _Closed = IF(Test10[ISCLOSED]=TRUE(),"Y","N")

RETURN SWITCH("Y",_NewOpportunity,"New Opportunity Created"
                 ,_Marketing,"Marketing Generated"
                 ,_Closed,"Closed"
             )

2. Now use the Matrix visual, having the new column created above as Rows and Created Date as Columns and Quantity as Values.

[Anonymous]

Hi, Thanks for the reply.

I've created date and closed date in fact table and currently i liked the close date to date dimension.

To calculate new opportunity created, i need to consider the Created date and to calculated closed, i need to conisder the close date.

I think in the given screenshot you only showing the created date. Please clarify.