Count of Open or Closed items based on 2 different Date Columns

[vinaydavid]

Hi Experts,

I have a requirement to find the count of open/close items based on 2 different date columns. Finding it hard as I am new to power bi.

Here is scenario:

Table:

Date Initiated	closed/open	closed
1/01/2019	open
1/01/2019	closed	1/02/2019
1/01/2019	open
1/02/2019	open
1/02/2019	closed	1/03/2019
1/02/2019	open

Expected Result:

Chart with Open/Closed Status (Clustered column bar). (Month on X-axis)

Jan - Open 2 Close 0

Feb - Open 4 Close 1

Mar - Open 4 Close 1

Open should give the count of all the previous months where 'Closed' column is BLANK.

And an underlying Table visualization(with few columns from query for further analysis - for users) should help to know, which are 'closed' and which are 'open' when we click on the Clustered bar.

Thanks for your support!

[jdbuchanan71]

You can use a date table to get what you are looking for.

Join your data table to the date table on the Date Initiated, this will be the active relationship.

Join your data table to the date table on the closed date, this relationship will be inactive but we will use it in a measure.

Open Count = 
    CALCULATE(
        COUNTROWS(Projects),
        Projects[open/close] = "Open"
    )

Closed Count = 
    CALCULATE(
        COUNTROWS(Projects),
        Projects[open/close] = "Closed",
        USERELATIONSHIP(Projects[closed],Dates[Date])
    )

I uploaded a sample .pbix file for you to look at.  

https://www.dropbox.com/s/8xy30j2qq0c8mi5/OpenOrClosed.pbix?dl=0

[vinaydavid]

Thanks jdbuchanan71,

Appreciate your time to respond and help me.

The interest/requirement is to look on the cumulative 'Open' items per month.

i.e., If in Jan - Open 2; Feb - Open 4(+ open from Jan); Mar - Open 5(+ open from Feb).......then in April it should show All the open items until April.

This data will help the Analysts to see why the issues are pile-up and still open.

Alternatively, there is an other column which help me to know which row is open or close.

So, can you help me know how to achieve the below thing....(Only Current month is needed based on Max of Closed date per Categry (3 ) - there exists 3 catgeories)

From the 'Closed date' column, it should pick the MAX date, and then display that Month in X-axis and corresponding closed items in that Month along with all the 'Open' items.

Something like this.....

Green - Close; Blue - Open

At the moment, I am able to show the open and closed items,But my struggle is with 2 points which are still open.

1. Based on the Category selected from the filter, the chart should pick the MAX Closed date in that category.

2. I have to show only the closed items in that month. The users are not interested to see for Eg: closed items in June, May or prior......

Appreciate your inputs...

Thanks again!

[jdbuchanan71 · Accepted solution]

I added a running total measure and a filter measure [DateRange] based on the max closed data of the selected category then applied that to the second chart that will shift the displayed month as you select a category.

https://www.dropbox.com/s/8xy30j2qq0c8mi5/OpenOrClosed.pbix?dl=0

Edit* tweaked it a bit to allow selection of multiple categories to drive the visual properly

[elchinitotwo]

Thank you so much for this solution - I'm fairly new to Power BI and this helped solve a business requirement. I added a table visual to my report that is filtered down based on the month highlighted on the bar chart, which is really convenient.

However, a user recently noticed that the table only shows rows for that month based on the open date, but not the closed date. In your sample report (to which I added a table), if you click on Jul-2019, it does not show the row with a closed date in Jul 2019. I believe this is because the active relationship is on the open date, because if I instead make the closed date relationship active, the table then only displays rows that were closed during that month.

I've been trying different things but can't seem to figure it out. How can I make it display the rows that were either opened OR closed in that month?

[vinaydavid]

Thanks a lot jdbuchanan71

Your solution worked for me!

Appreciate your time and effort in helping me.

This community has been a great place for beginners to learn and experts to share their knowledge.

Cheers!

David