Forum Discussion
sales backlog
Hi,
I have an opportunity table from Salesforce and i'd like to see in the future how much in we expect to bill per customer and Job over time. Each customer(AccountID) has jobs (Job_Code__c) that we win. The Salesman_Estimate__c is how much we think we will make over the life of the job from start (Anticipated_Job_Start_Date) to finish (Anticipated_End_Date__c). We'd like to know on each day how much we plan to bill our customers. How can I map this over time?
| AccountId | Job_Code__c | Anticipated_Job_Start_Date__c | Anticipated_End_Date__c | Salesman_Estimate__c |
| 0010Z00001wFVzXQAW | G-38759 | 12/10/2024 0:00 | 12/10/2024 0:00 | $9,200 |
| 001i000000VTEbuAAH | G-38855 | 12/10/2024 0:00 | 12/10/2024 0:00 | $6,000 |
| 001i000000VTFTAAA5 | R-38823 | 12/10/2024 0:00 | 12/10/2024 0:00 | $3,800 |
| 0010Z00002D0bYtQAJ | R-38027 | 12/9/2024 0:00 | 12/9/2024 0:00 | $50,000 |
| 0013r00002NHjfKAAT | G-38726 | 12/9/2024 0:00 | 12/19/2024 0:00 | $25,000 |
| 0013r00002ZaVPMAA3 | G-36257 | 12/9/2024 0:00 | 12/9/2024 0:00 | $12,000 |
| 001i000000VTFFsAAP | T-38686 | 12/9/2024 0:00 | 12/10/2024 0:00 | $39,830 |
| 001Pg000002bjyFIAQ | G-38809 | 12/9/2024 0:00 | 12/20/2024 0:00 | $20,000 |
| 001Pg0000095zeFIAQ | G-38425 | 12/9/2024 0:00 | 12/21/2024 0:00 | $100,000 |
| 001Pg00000PLUO6IAP | G-38700 | 12/9/2024 0:00 | 12/10/2024 0:00 | $15,000 |
| 001i000000VTFPCAA5 | W-37649 | 12/8/2024 0:00 | 12/12/2024 0:00 | $123,661 |
| 001Pg00000DWMzXIAX | G-38800 | 12/6/2024 0:00 | 12/6/2024 0:00 | $3,500 |
| 0013r00002l2zknAAA | G-38849 | 12/5/2024 0:00 | 12/5/2024 0:00 | $2,500 |
| 001i000000VTFefAAH | BR-12206 | 12/5/2024 0:00 | 1/1/2025 0:00 | $20,000 |
| 001i000001DK7l0AAD | G-38804 | 12/5/2024 0:00 | 12/20/2024 0:00 | $13,000 |
| 001Pg00000QJNoAIAX | BR-12193 | 12/5/2024 0:00 | 2/26/2025 0:00 | $70,000 |
| 001Pg00000QJNoAIAX | BR-12194 | 12/5/2024 0:00 | 2/26/2025 0:00 | $60,000 |
| 0010Z000028unnAQAQ | R-38827 | 12/4/2024 0:00 | 12/10/2024 0:00 | $15,000 |
| 001i000000VTFfxAAH | W-38858 | 12/4/2024 0:00 | 2/4/2026 0:00 | $427,812 |
| 001i000000VTFIHAA5 | G-38854 | 12/4/2024 0:00 | 12/4/2024 0:00 | $3,500 |
| 001i000000VTFILAA5 | G-38853 | 12/4/2024 0:00 | 12/5/2024 0:00 | $4,800 |
| 001Pg00000Abd0EIAR | G-38859 | 12/4/2024 0:00 | 12/4/2024 0:00 | $1,200 |
| 0010Z00001wFVzXQAW | G-38757 | 12/3/2024 0:00 | 12/3/2024 0:00 | $9,200 |
| 001i000000VTELjAAP | G-38862 | 12/3/2024 0:00 | 12/3/2024 0:00 | $4,000 |
| 001i000000VTFfxAAH | W-37637 | 12/3/2024 0:00 | 12/6/2024 0:00 | $144,825 |
| 001i000000VTFfxAAH | W-38555 | 12/3/2024 0:00 | 12/3/2024 0:00 | $30,000 |
| 001i000000VTGMcAAP | G-38846 | 12/3/2024 0:00 | 12/4/2024 0:00 | $35,000 |
| 001Pg00000TiWeeIAF | G-38845 | 12/3/2024 0:00 | 12/3/2024 0:00 | $5,000 |
| 001Pg00000U0F2gIAF | G-38863 | 12/3/2024 0:00 | 12/3/2024 0:00 | $2,300 |
i've seen other posts like this one: https://community.fabric.microsoft.com/t5/Desktop/Calculating-Total-Project-Backlog-Per-Month/m-p/4266173#M1340414 but I would like mine per day. Even when I try to use the measures in this answer and do this by month, I get an error "cannot convert value of type Text to type Number" and I don't understand where that error is coming from.
Would something like this work?
I added 2 columns to the fact table.
Workdays = NETWORKDAYS( [Anticipated_Job_Start_Date__c], [Anticipated_End_Date__c] ) PerDay = DIVIDE( [Salesman_Estimate__c], [Workdays] )Next I added these 2 measures.
Job Value Dist. (inner) = VAR _SelDt = MAX( 'Date'[Date] ) VAR _WkDay = MAX( 'Date'[Working Day] ) VAR _Result = IF( _WkDay = 1, CALCULATE( SUM( 'FactTable'[PerDay] ), FILTER( ALLEXCEPT( 'FactTable', 'FactTable'[Job_Code__c] ), 'FactTable'[Anticipated_Job_Start_Date__c] <= _SelDt && 'FactTable'[Anticipated_End_Date__c] >= _SelDt ) ) ) RETURN _ResultJob Value Distribution = SUMX( FILTER( 'Date', 'Date'[Working Day] = 1 ), [Job Value Dist. (inner)] )Let me know if you have any questions.
4 Replies
Hi MoMrCrane
We dont have acces to your salesfoce account but you can post a sample data and please not an image. Please see this post. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Thanks for the reminder, i updated the original post. And i have some sample data below:
AccountId Job_Code__c Anticipated_Job_Start_Date__c Anticipated_End_Date__c Salesman_Estimate__c 0010Z00001wFVzXQAW G-38759 12/10/2024 0:00 12/10/2024 0:00 $9,200 001i000000VTEbuAAH G-38855 12/10/2024 0:00 12/10/2024 0:00 $6,000 001i000000VTFTAAA5 R-38823 12/10/2024 0:00 12/10/2024 0:00 $3,800 0010Z00002D0bYtQAJ R-38027 12/9/2024 0:00 12/9/2024 0:00 $50,000 0013r00002NHjfKAAT G-38726 12/9/2024 0:00 12/19/2024 0:00 $25,000 0013r00002ZaVPMAA3 G-36257 12/9/2024 0:00 12/9/2024 0:00 $12,000 001i000000VTFFsAAP T-38686 12/9/2024 0:00 12/10/2024 0:00 $39,830 001Pg000002bjyFIAQ G-38809 12/9/2024 0:00 12/20/2024 0:00 $20,000 001Pg0000095zeFIAQ G-38425 12/9/2024 0:00 12/21/2024 0:00 $100,000 001Pg00000PLUO6IAP G-38700 12/9/2024 0:00 12/10/2024 0:00 $15,000 001i000000VTFPCAA5 W-37649 12/8/2024 0:00 12/12/2024 0:00 $123,661 001Pg00000DWMzXIAX G-38800 12/6/2024 0:00 12/6/2024 0:00 $3,500 0013r00002l2zknAAA G-38849 12/5/2024 0:00 12/5/2024 0:00 $2,500 001i000000VTFefAAH BR-12206 12/5/2024 0:00 1/1/2025 0:00 $20,000 001i000001DK7l0AAD G-38804 12/5/2024 0:00 12/20/2024 0:00 $13,000 001Pg00000QJNoAIAX BR-12193 12/5/2024 0:00 2/26/2025 0:00 $70,000 001Pg00000QJNoAIAX BR-12194 12/5/2024 0:00 2/26/2025 0:00 $60,000 0010Z000028unnAQAQ R-38827 12/4/2024 0:00 12/10/2024 0:00 $15,000 001i000000VTFfxAAH W-38858 12/4/2024 0:00 2/4/2026 0:00 $427,812 001i000000VTFIHAA5 G-38854 12/4/2024 0:00 12/4/2024 0:00 $3,500 001i000000VTFILAA5 G-38853 12/4/2024 0:00 12/5/2024 0:00 $4,800 001Pg00000Abd0EIAR G-38859 12/4/2024 0:00 12/4/2024 0:00 $1,200 0010Z00001wFVzXQAW G-38757 12/3/2024 0:00 12/3/2024 0:00 $9,200 001i000000VTELjAAP G-38862 12/3/2024 0:00 12/3/2024 0:00 $4,000 001i000000VTFfxAAH W-37637 12/3/2024 0:00 12/6/2024 0:00 $144,825 001i000000VTFfxAAH W-38555 12/3/2024 0:00 12/3/2024 0:00 $30,000 001i000000VTGMcAAP G-38846 12/3/2024 0:00 12/4/2024 0:00 $35,000 001Pg00000TiWeeIAF G-38845 12/3/2024 0:00 12/3/2024 0:00 $5,000 001Pg00000U0F2gIAF G-38863 12/3/2024 0:00 12/3/2024 0:00 $2,300 Would something like this work?
I added 2 columns to the fact table.
Workdays = NETWORKDAYS( [Anticipated_Job_Start_Date__c], [Anticipated_End_Date__c] ) PerDay = DIVIDE( [Salesman_Estimate__c], [Workdays] )Next I added these 2 measures.
Job Value Dist. (inner) = VAR _SelDt = MAX( 'Date'[Date] ) VAR _WkDay = MAX( 'Date'[Working Day] ) VAR _Result = IF( _WkDay = 1, CALCULATE( SUM( 'FactTable'[PerDay] ), FILTER( ALLEXCEPT( 'FactTable', 'FactTable'[Job_Code__c] ), 'FactTable'[Anticipated_Job_Start_Date__c] <= _SelDt && 'FactTable'[Anticipated_End_Date__c] >= _SelDt ) ) ) RETURN _ResultJob Value Distribution = SUMX( FILTER( 'Date', 'Date'[Working Day] = 1 ), [Job Value Dist. (inner)] )Let me know if you have any questions.
