powerbi - How can you plot quantity opened and qty closed each month on one graph using "Opened date" and &

I have a table similar to that in the image below and I want to plot a stacked bar graph showing quantity opened and qty closed in each month using the "Opened Date" and "Closed Date" columns. I have achieved the graph by duplicating the main table twice, creating a table showing only the "Item ID" and "Date Opened" and another showing only "Item ID" and "Date Closed" and linked these to a date table. the issue is that I cannot create a relationship between these 2 new tables with the main data set via the "Item ID" field (as it causes an impossible circular relationship).

Diagram of data relationships

I have a table similar to that in the image below and I want to plot a stacked bar graph showing quantity opened and qty closed in each month using the "Opened Date" and "Closed Date" columns. I have achieved the graph by duplicating the main table twice, creating a table showing only the "Item ID" and "Date Opened" and another showing only "Item ID" and "Date Closed" and linked these to a date table. the issue is that I cannot create a relationship between these 2 new tables with the main data set via the "Item ID" field (as it causes an impossible circular relationship).

Diagram of data relationships

• powerbi
• visualization
• relationship

Share Improve this question Follow edited Mar 27 at 9:37 DarkBee 15.5k88 gold badges7272 silver badges117117 bronze badges asked Mar 25 at 11:08 LunchboxthLunchboxth 1 1

• Please do not upload images of code/data/errors. – DarkBee Commented Mar 27 at 9:37

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

If its just the quantity per month you are interested I would unpivot the the Open Date and Close Date fields in power query and then use the Date type as the legend. If you would like help on unpivot let me know

Item ID	Date type	Date	Owner
a	Close Date	2/1/25	A
a	Open Date	1/1/25	A
b	Close Date	2/1/25	B
b	Open Date	3/1/25	B