Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

Any ideas any thoughts?

 

Anonymous
Not applicable

Any thoughts?

Anonymous
Not applicable

@Anonymous : 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.