Duration per month between dates and for a specific year X daily cost per period

[marcosvcortes00]

On 2022-08-10 I posted an issue about "Duration per month between dates and for a specific year" and it has been resolved. The measure presented in Power BI was a success!

Look:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Duration-per-month-between-dates-and-for-a-sp...

But now I came across the following:

Considering that the projects had a daily cost that varied according to a certain period, how could we know the total value of each project by month and year?

Example:
Project        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

[wdx223_Daniel · Accepted solution]

Efficiency aside, just offer some ideas

firstly create a date table, DatesTable

then create a 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]
        )
)

the result look like this

[CNENFRNL]

[marcosvcortes]

Duration per month between dates and for a specific year X daily cost per period
I appreciate you answering me, but I wanted to improve my question.

The idea would be to multiply the costs below, considering each period (variable cost):

Example:
Resource Cost Cost Start Period End Period
1 2.00 01/12/2022 15/01/2023
1 3.00 16/01/2022 30/01/2023
2 2.00 01/12/2023 15/12/2023
3 5.00 16/12/2023 30/01/2024
3 3.00 01/10/2023 15/11/2023
4 4.00 16/10/2023 30/11/2023
4 1.00 01/12/2022 30/06/2023
D 2.00 01/07/2022 30/01/2024

Considering the periods of each project:

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

I would like to know the total cost of each project.

Note that project A uses feature 1, which has two costs: 2.00, between 12/01/2022 to 15/01/2023 and 3.00, between 16/01/2022 to 30/01/2023.The total of project A should be:

31 * 2 = 62

15 * 2 = 30
16 * 3 = 48

62 + 30 + 48 = 140

[wdx223_Daniel · Accepted solution]

Efficiency aside, just offer some ideas

firstly create a date table, DatesTable

then create a 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]
        )
)

the result look like this

[marcosvcortes · Accepted solution]

Thank you. I'll be testing the solution.

[marcosvcortes]

It would be interesting to measure a DAX that was in accordance with the solution given by CNENFRNL, available in PBIX at:

Solved: Duration per month between dates and for a specifi... - Microsoft Power BI Community

[amitchandak]

@marcosvcortes00 , refer if one of these approaches can help

Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...