Forum Discussion
How to create clustered column chart using 2 date columns from the data table
The final result should look like this (Done using Excel) with running 12 months data up to current month.
The dataset has 2 columns. One has Opened Date and Closed Date. Both are Date columns with Date Heirarchies in Power BI Desktop but in the dataset itself, the month and year columns are separate for each. So there is a Opened Date, Opened Year, Closed Date, Closed Year.
I have 2 problems
1st Problem
I am able to recreate a column chart with the correct data for each column (Opened only or Closed only). Doing a few searches I see that I need to unpivot the Opened Date and Closed date columns in Power Query but when I do that and create the clustered column, the numbers no longer match.
How do I fix this? Data set sample at the bottom.
2nd Problem
The Opened in Month data is a calculated data set. The calculation is as follows
=June Opened in Month+July Opened-July Closed
| June | July | August | September | October | November | December | January | February | March | April | May | June | |
| Opened | 5 | 6 | 8 | 6 | 10 | 11 | 5 | 4 | 6 | 3 | 3 | 6 | |
| Closed | 4 | 9 | 3 | 4 | 6 | 9 | 6 | 9 | 2 | 9 | 6 | 8 | |
| Open in Month | 27 | 24 | 29 | 31 | 35 | 37 | 36 | 31 | 35 | 29 | 26 | 24 | 24 |
How do I calculate this in DAX?
Sample of how Data looks like
| Customer Name | Unique ID | Open Year | Close Year | Opened Date | Close Date |
| ABCD | 1111 | 2023 | null | 10/01/2023 | null |
| ABCD | 1112 | 2024 | null | 11/01/2023 | null |
| ABCD | 1113 | 2024 | null | 12/01/2023 | null |
| ABCD | 1114 | 2024 | null | 01/01/2024 | null |
| ABCD | 1115 | null | 2023 | null | 12/01/2023 |
| ABCD | 1116 | null | 2024 | null | 01/01/2024 |
| ABCD | 1117 | null | 2024 | null | 02/01/2024 |
| ABCD | 1118 | null | 2024 | null | 03/01/2024 |
| ABCD | 1119 | 2024 | null | 02/01/2024 | null |
| ABCD | 1120 | 2024 | null | 03/01/2024 | null |
| ABCD | 1121 | null | 2024 | null | 04/01/2024 |
4 Replies
Hi rouelandrew
1st Problem
In Power Query, select the Opened Date and Closed Date columns, then use the "Unpivot Columns" option. This should transform your data into a long format, where you have a column for the attribute (Opened/Closed) and a column for the value (the actual dates).
2nd Problem
You might consider creating a measure that calculates the running total of opened cases and then subtracts the closed cases for each month.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, as mentioned in my post I did try Unpivot but when I do that the data doesn't line up or is accurate. What am I doing wrong?
