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
