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 table contains information on plants :

LocationsPowerStartDateEndDate
Plant112002016-05-012019-12-01
Plant290002018-01-01 
Plant360002018-08-012022-11-01
Plant424002021-01-01 
Plant5150002024-06-01 

 

The expected result is :

YearCumulatedPower
20161200
20171200
201816200
201916200
202015000
202117400
202217400
202311400
202429400

 

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

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

5 Replies

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