marcosvcortes00 Tracker

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

marcosvcortes00 — Mon, 09 Oct 2023 11:24:16 GMT

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]
)
)

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Duration-per-month-between-dates-and-for-a-specific-year-X-daily/m-p/2884246#M93438

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

Distribute costs by month taking into account the exchange rate

marcosvcortes00 — Wed, 12 Oct 2022 03:23:56 GMT

Como posso distribuir os valores do projeto por mês considerando várias taxas de câmbio?

Temos:

Tabela com os valores de troca por mês.

Tabela 1

Custo de cada recurso com os respectivos valores por moeda.

Tabela 2

Duração de cada projeto.

Tabela 3

O resultado final seria assim:

Mas eu não sou capaz de converter todo o valor em dólar ou real.

Veja se posso distribuir os valores de duração de cada projeto por mês (exemplo Recurso B)**:

**Measurement by a BI specialist.

With the duration values, now just multiply by the price of each product (table 2), but I must consider the coins. To present the total value in dolar would be to convert the currency in real to dolar, which would be to divide by the exchange rate.

Translated text.

Re: Duration per month between dates and for a specific year.

marcosvcortes00 — Mon, 10 Oct 2022 14:15:54 GMT

Worked perfectly! Thank you for your help.

(Translated text)

Re: Duration per month between dates and for a specific year.

marcosvcortes00 — Mon, 10 Oct 2022 13:58:40 GMT

Very very good! Worked perfectly! It's good to be able to count on people like you. Thank you for being able to share some of your knowledge here in the community.

(Translated text)

Re: Duration per month between dates and for a specific year.

marcosvcortes00 — Sat, 08 Oct 2022 20:49:58 GMT

Thank you for your answer.
As soon as I have the company computer, I will be applying your suggestion.
But with this I can distribute the duration values per month?

Example:

Project A
January
30 days

Project C
October November
31 days 30 days

Re: Duration per month between dates and for a specific year.

marcosvcortes00 — Sat, 08 Oct 2022 20:46:41 GMT

Thank you for your answer.
As soon as I have the company computer, I will be applying your suggestion.
But with this I can distribute the duration values per month?

Example:

Project A
January
30 days

Project C
October November
31 days 30 days

Duration per month between dates and for a specific year.

marcosvcortes00 — Sat, 08 Oct 2022 15:04:22 GMT

Duration per month between dates and for a specific year.

Hello, I am in the following situation:

Project Start End Duration
A 01/12/2022 30/01/2023 60
B 01/12/2023 30/01/2024 60
C 01/10/2023 30/11/2023 60
D 01/12/2022 30/01/2024 425

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

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

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

(Forgive for my English).