Project spans two years or more, based on start and end date need to know month in each year

[mattbales]

We have a list of "Projects" with a start date and an end date.  Many of the projects last for multiple years. 

For example, Project A starts on 02/15/2021 and will end on 8/20/2023.  We are trying to create a formula that says how many months of the project's revenue can be attributed to calendar years 2021, 2022, and 2023. 

2021 = 10 months

2022 = 12 months

2023 = 8 months

I do not need it to be any more specific than money. I am new to PowerBI so any advice is welcomed. 

[mattbales]

Thanks for your answer. It was most helpful.  Now, if you see this, I would like to see if you know how to create a matrix table.

For example, Project A starts on 02/15/2021 and will end on 8/20/2023. The total project duration is 31 months.  The total project value is $31,000. The customer will pay $1,000 per month.  I would like to have a table that shows from January (column name) through December that Project is worth $1,000 per month.  

I have seen others write similar scenarios but nothing as similar to mine.  

[mattbales]

@lbendlin 

You are correct, 2021 should have 11 months.  Sloppy math on my end. 

We do have a separate discounted data table for dates.  We have columns in our current data for start date and end date for the year.  

[v-yingjl · Accepted solution]

Hi @mattbales ,

You can create measures like this to calculate:

2021 month = 
VAR _start =
    SELECTEDVALUE ( 'Table'[Start date] )
VAR _end =
    SELECTEDVALUE ( 'Table'[End date] )
VAR _date =
    DATE ( 2021, 12, 31 )
RETURN
    IF ( _date >= _start && _date <= _end, DATEDIFF ( _start, _date, MONTH ) + 1 )

2022 month = 
VAR _start =
    SELECTEDVALUE ( 'Table'[Start date] )
VAR _end =
    SELECTEDVALUE ( 'Table'[End date] )
VAR _date1 =
    DATE ( 2021, 12, 31 )
VAR _date2 =
    DATE ( 2022, 12, 31 )
RETURN
    IF (
        _date1 > _start
            && _date2 > _end,
        DATEDIFF ( _date1, _end, MONTH ),
        IF ( _date1 > _start && _date2 < _end, DATEDIFF ( _date1, _date2, MONTH ) )
    )

2023 month = 
VAR _start =
    SELECTEDVALUE ( 'Table'[Start date] )
VAR _end =
    SELECTEDVALUE ( 'Table'[End date] )
VAR _date =
    DATE ( 2022, 12, 31 )
RETURN
    IF ( _date > _start && _date < _end, DATEDIFF ( _date, _end, MONTH ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please explain why 2021 has 10 months and not 11.

Does your data model have a disconnected table with a year column?