Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |