Duration per month between dates and for a specific year.

[marcosvcortes00]

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

[Stachu · Accepted solution]

you cound do something like this (as calculated column):

Duration in 2023 = COUNTROWS(FILTER(GENERATESERIES([Start],[End],1),YEAR([Value]) = 2023))

Is this approach fine, or do you need a measure?

[CNENFRNL]

[marcosvcortes00]

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

[CNENFRNL · Accepted solution]

A seemingly easy question, it's in fact beyond quite many seasoned PBI users.

[marcosvcortes00]

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)

[Stachu · Accepted solution]

you cound do something like this (as calculated column):

Duration in 2023 = COUNTROWS(FILTER(GENERATESERIES([Start],[End],1),YEAR([Value]) = 2023))

Is this approach fine, or do you need a measure?

[marcosvcortes00]

Worked perfectly! Thank you for your help.

(Translated text)

[marcosvcortes00]

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