Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulate values between two date fields

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 ...
Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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 ()
    )
    )
    )
    )
     
     
     
     
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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 ()
)
)
)
)
 
 
 
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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