Re: Duration and cost of projects per month in a s...

marcosvcortes · ‎10-06-2023

Hello, I am in the following situation:

Project Start End Duration Resources
1 01/12/2022 30/01/2023 60     A
2 01/12/2023 30/01/2024 60     B
3 01/10/2023 30/11/2023 60     C
4 01/12/2022 30/01/2024 425    D

I need to inform in a table the duration and cost per month of each project, but only for the months of the year 2023.

Example: Project A: will run for 30 days in January 2023, while project C will run for 60 days.

Note that there are projects that start or end in 2023, but there are projects that start and end in other years, however, they have work in 2023, as is the case with project D.

Considering that the daily cost of each resource varies according to the table below, which DAX measure could present a table showing the monthly cost of each project?

Table:

Resources Daily Cost Start Period End Period

A 2.00 12/01/2022 01/15/2023

A 3.00 12/16/2022 01/30/2023

B 2.00 12/01/2023 12/15/2023

B 5.00 12/16/2023 01/30/2024

C 3.00 10/01/2023 11/15/2023

C 4.00 10/16/2023 11/30/2023

D 1.00 12/01/2022 06/30/2023

D 2.00 01/07/2022 30/01/2024

lbendlin · ‎10-08-2023

Are you sure you want to do this by month? Months have different number of days. You have weekends and holidays etc.

marcosvcortes00 · ‎10-09-2023

Yes I have. There's even someone who helped me a while ago, but I can't create a table of the amounts distributed per month. See the DAX measure:

CostTotal =
SUMX (
'ProjectTable',
VAR _dates =
DATESBETWEEN ( 'DatesTable'[Date], 'ProjectTable'[Start], 'ProjectTable'[End] )
RETURN
SUMX (
FILTER ( CostTable, CostTable[Resource] = 'ProjectTable'[Resource] ),
COUNTROWS (
INTERSECT (
_dates,
DATESBETWEEN (
'DatesTable'[Date],
CostTable[Start Period ],
CostTable[End Period]
)
)
) * CostTable[Cost]
)
)