cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azat Regular Visitor
Regular Visitor

Please Help

Hello everyone!

 

I fighting with one problem that cannot solve.

 

We getting "leads" from different web sites, these leads are converted into "opportunities" and then into "closed wons". We found out that 90% of closed opportunities are closing within 101 days (opportunities->closed won), and 90% of closed "leads" are closing within 107 days (leads->closed won). Created date of a Lead or opp, closing in June, can be from any possible month. In other words, lead from March can be closed in June, as well as Feb lead can be closed in June, same with opps.

So the question is how can I create a table where I have Closed wons (from 1st of May till 31st of May), Opps (# of opps from May-101days till 31 of May) ,Leads (# of Leads from May -107days till 31 of May)?

I was able to make only for last month calculation

Leads_within_107(90.02%)days = CALCULATE(countx(Leads, Leads[CreatedDate]),

datevalue(FORMAT(value(
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2))))))-107, "mm/dd/yyyy hh:mm:ss"))
<=Leads[CreatedDate]&&
Leads[CreatedDate]<=
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2))))),

datevalue(FORMAT(value(
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2))))))-101, "mm/dd/yyyy hh:mm:ss"))
<=Leads[opp_created_date]&&
Leads[opp_created_date]<=
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2))))),
Leads[leadstatus] in {"lost", "won"})



Opps_within_101(89.98%)_days = CALCULATE(countx(Leads, Leads[opp_created_date]),
datevalue(FORMAT(value(
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2))))))-101, "mm/dd/yyyy hh:mm:ss"))
<=Leads[opp_created_date]&&
Leads[opp_created_date]<=
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2)))))
,

(iF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,1),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,1),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, 1)))
<=Leads[opp_close_date]&&
Leads[opp_close_date]<=
IF(MONTH(TODAY())=1,DATE(YEAR(TODAY())-1,11,day(EOMONTH(today(),-2))),
IF(MONTH(TODAY())=2,DATE(YEAR(TODAY())-1,12,day(EOMONTH(today(),-2))),
DATE(YEAR(TODAY()),MONTH(TODAY())-2, day(EOMONTH(today(),-2)))))),
Leads[stages_shortened] in {"lost", "won"}
)

**Leads[leadstatus] = if(value(NOW())-VALUE(Leads[CreatedDate])>11, "lost", if (Leads[opp_created_date]<>BLANK()&&value(Leads[opp_created_date])-VALUE(Leads[CreatedDate])>=0&&value(Leads[opp_created_date])-VALUE(Leads[CreatedDate])<=11,"won", "open"))

**Leads[stages_shortened] = if(Leads[Opp_stage]="closed won", "won", if(Leads[Opp_stage]="closed lost", "lost", "open"))


**11 days means that within 11 days 90% of leads converts to opportunity
3 REPLIES 3
Highlighted
Azat Regular Visitor
Regular Visitor

Re: Please Help

Any thoughts?

Re: Please Help

@Azat : If it is really urgent to solve raise ticket with MS support they can help you where you will have dedicated person working on your scnerio . Else we need to wait unless some one replies . If you pro users you can utilize support from MS

Azat Regular Visitor
Regular Visitor

Re: Please Help

Any ideas any thoughts?