Cumulate values between two date fields

[Anonymous]

Hello,

I face an issue that looks simple but I don't find the trick.

My wish is to display on a visual the power cumulated year by year of the plants between StartDate and EndDate

Our first table contains information on plants :

Locations	Power	StartDate	EndDate
Plant1	1200	2016-05-01	2019-12-01
Plant2	9000	2018-01-01
Plant3	6000	2018-08-01	2022-11-01
Plant4	2400	2021-01-01
Plant5	15000	2024-06-01

The expected result is :

Year	CumulatedPower
2016	1200
2017	1200
2018	16200
2019	16200
2020	15000
2021	17400
2022	17400
2023	11400
2024	29400

I tried many things and each time I'm really close from the expected result but there is always a little problem happening.

Only one rule to apply for my case : we can use a calendar table but we can't add a relationship between the calendar table and the table which contains the startDate and the EndDate.

How would you proceed ?

Thanks in advance.

Best regards,

Cado

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Power Measure =

IF (

ISFILTERED ( Dates ),

CALCULATE (

SUMX (

Plants,

Plants[Power]

),

FILTER (

Plants,

Plants[StartDate]

<= MAX ( Dates[Date] )

&& OR (

Plants[EndDate]

>= MIN ( Dates[Date] ),

Plants[EndDate]

= BLANK ()

)

)

)

)

https://www.dropbox.com/s/cwfcqul7vrbb2di/cado.pbix?dl=0 

[Anonymous]

Hi @Jihwan_Kim 

Thank you for helping me, the measure you propose works fine until 2021 but the following years are not displayed in the visual. Do you know where could the problem come from ?

In the database there are a lot of rows with StartDate in 2022 2023 2024 2025 but nothing on the visual.
I already tried the "Show items with no data" option and still nothing after 2021.

I also check the external filters and nothing should be disturbing the measure.

[Jihwan_Kim]

Hi, @Anonymous 

Thanks for your feedback.

Please share your sample pbix file's link here, then I can try to look into it to come up with a solution.

[Anonymous]

@Jihwan_Kim 

Sorry the problem came from my calendar table (parenthesis misplaced in the CALENDAR formula) so the last date was in 2021 instead of 2025 😅

Thank you again for the measure and have a nice day !

Best regards,

Cado

[amitchandak]

@Anonymous , refer if this blog can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785