topic Re: Duration per month between dates and for a specific year X daily cost per period in DAX Commands and Tips

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

marcosvcortes00 — Fri, 04 Nov 2022 01:52:05 GMT

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-specific-year/m-p/2827338#M89926

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

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

amitchandak — Fri, 04 Nov 2022 02:11:18 GMT

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-or-end-date/ba-p/1503785

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

marcosvcortes — Fri, 04 Nov 2022 10:20:15 GMT

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

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

CNENFRNL — Sun, 06 Nov 2022 02:19:07 GMT

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

marcosvcortes — Mon, 07 Nov 2022 14:01:07 GMT

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

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

wdx223_Daniel — Tue, 08 Nov 2022 01:51:30 GMT

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

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

marcosvcortes — Wed, 09 Nov 2022 12:23:25 GMT

Thank you. I'll be testing the solution.