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.
Hi, I'm trying to display 2 lines of values based on different dates. The data is as shown as below.
StartDate | EndDate | StartValue | EndValue |
01 Jan 2021 | 15 Jan 2021 | 5 | 6 |
01 May 2021 | 25 Jun 2021 | 15 | 21 |
01 Aug 2021 | 01 Sep2021 | 24 | 26 |
now I want to put in the same line chart both lines. However, if I use StartDate in the X Axis, the EndValue won't be correctly displayed and the same will happend with the Start Value. For example, in the image below the 01/Sep/2021 it is displaying 24 and 26 when it should be only 26. Image below for reference.
I'm getting this
and I want to get something like this
I believe I need a DateTable from the dates I am interested in and make a relationship with my dates table but I've tried relationship with Start and End Date and It doesn't work.
Any idea is more than welcome
Thanks!
Solved! Go to Solution.
Hi, @derekma19
If you have a calendar table, you can try add another inactive relationship between ‘Table’[EndDate] and 'Calendar'[Date].
Then put 'Calendar'[Date] to the x-axis of line chart , and put the following two measure to the y-axis of line chart.
M_startvalue = SUM('Table'[StartValue])
M_EndValue = CALCULATE(SUM('Table'[EndValue]),USERELATIONSHIP('Table'[EndDate],'Calendar'[Date]))
Please check attached pbix for more details.
Best Regards,
Community Support Team _ Eason
Hi, @derekma19
If you have a calendar table, you can try add another inactive relationship between ‘Table’[EndDate] and 'Calendar'[Date].
Then put 'Calendar'[Date] to the x-axis of line chart , and put the following two measure to the y-axis of line chart.
M_startvalue = SUM('Table'[StartValue])
M_EndValue = CALCULATE(SUM('Table'[EndValue]),USERELATIONSHIP('Table'[EndDate],'Calendar'[Date]))
Please check attached pbix for more details.
Best Regards,
Community Support Team _ Eason
@derekma19 , refer if these approaches can help
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
Tables way
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |