cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Dates.

Hello,

I am trying to CALCULATETABLE (sample below) to iterate through the FIRSTDATE (START_DATE) and LASTDATE (END_DATE) and straight-line REVENUE and LABOR over each monthly period so I can tie the proposed project data to monthly actuals to a date dimension table.

 

PROPOSED PROJECT DATA

PROJ_ID     START_DATE    END_DATE     REVENUE     LABOR

11111         8/15/2020        8/14/2021      1,000,000    750,000

11111         8/15/2021        8/14/2022      1,000,000    750,000

11112         8/15/2020        8/14/2021      1,000,000    750,000

11113         8/15/2020        8/14/2021      1,000,000    750,000

 

I thank you in advanced. 

 

12 REPLIES 12
Community Support
Community Support

Hi @walkerb32 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

Hello @RicoZhou!

 

One last item:  In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP)  Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?

 

As an example, project 25011 should only have 6 line items:

 

Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16

 

I appriciate all the help! 

 

Brad 

@RicoZhou Thank you and it is VERY close.  

 

One last item:  In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP)  Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?

 

As an example, project 25011 should only have 6 line items:

 

Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16

 

I appreciate all the help! 

 

Brad 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors