Monthly Project Cost/Revenue Calculation

[ghdunn]

I am working on a project forecasting tool in Power Apps/Model Driven Apps, but have come across some reporting challenges that are beyond my skills (though thanks to @Jimmy801  I am moving slowly up the curve in that I am working with Table.FromColumns but it will be some time before I can master the following:+)

He has provided an answer to one challenge of spreading total project costs over  a number of months:

https://community.powerbi.com/t5/Power-Query/Pro-rate-project-value-over-a-number-of-months/m-p/9289...

However, I now move on to another challenge of creating total, monthly project costs from the bottom up calculation of individual resources.

If you have a resource at a daily rate of $500, and assign them to a date range of 12th Feb through to 31st May, how can you calculate monthly charges, according to working days.

Input:

Resource, Rate, Start, End

A,$500,12/2/20,31/5/20

B,$200,29/2/20,15/5/20

Expected Output:

Month,Resource,TotalCharge

Feb,A,6500   (being 13 working days at 500)

Feb,B,NIL      (being no working days in Feb)

Mar,A     etc

Thanks!

Ged

[v-lili6-msft · Accepted solution]

hi  @ghdunn 

For your case, you could try this way as below:

Step1:

You need a calendar date table, you could use this formula to create this table:

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"Weekday",WEEKDAY([Date],2))

Step2:

Create a new table as below:

New table = FILTER(CROSSJOIN('Table','Date'),'Date'[Date]>='Table'[Start]&&'Date'[Date]<='Table'[End])

Step3:

Then create a TotalCharge measure as below:

TotalCharge = CALCULATE(SUM('New table'[Rate]),FILTER('New table','New table'[Weekday] in {1,2,3,4,5}))

Result:

Regards,

Lin

[Anonymous]

Hi v-lili6-msft,

I have a similar scenario with a sample data as below

Project ID	Project Name	Start Date	End Date	Daily Cost
1	A	01-01-19	31-12-26	2000
2	B	01-06-21	05-12-23	1000
3	C	15-04-18	18-10-22	7500

PREVIEW

I want to get the respective Cost dynamicaaly based on Date range selected.

Is it possible to achieve it using DAX measure instead of creating a new table.
Here's my sample DAX measure which is giving me wrong result.

Cost =

var MaxDate = Max('Date'[Date])

var MinDate = Min('Date'[Date])

var Costt = CALCULATE(SUM(Project[Daily Cost]), ALL('Date'), Project[Start Date]<=MinDate && Project[End Date]>=MaxDate)

return Costt

Any help would be appreciable.

Regards,

Prajna