How to Count Indiviudal Date Occurances

[Mark_Berry]

Have a data set that contains both first date opened and last date closed for a series of enquires.
These columns are formated as DD-MMM-YY. 

I am attempting to measure how many enquires are opened and closed per month, however when I attempt to count/distinct count the dates by month and year I get incorrect counts, as an example I have 10 enquires opened during January 4 of which are duplicate dates yet I recieve a result of 8 from Power BI, so I am unsure as to what specific enquires are being missed. 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result as well.

[Mark_Berry]

Reference	Date First Opened	Date Last Closed
100	16/09/2022	09-Dec-22
101	19/09/2022	24-Oct-23
102	20/09/2022	31-Jan-23
103	21/09/2022	24-Oct-23
104	21/09/2022	03-Feb-23
105	21/09/2022	06-Dec-22
106	28/09/2022	18-Nov-22
107	4/10/2022	20-Dec-22
108	4/10/2022	18-Nov-22
109	6/10/2022	08-Jun-23
110	10/10/2022	06-Dec-22
111	10/10/2022	06-Dec-22
112	13/10/2022	21-Mar-23
113	18/10/2022	16-Jan-23
114	18/10/2022	18-Nov-22
115	27/10/2022	12-Jan-24
116	28/10/2022	13-Sep-23
117	14/11/2022	09-Dec-22
118	15/11/2022	02-Feb-23
119	15/11/2022	09-Dec-22
120	18/11/2022	01-Mar-23
121	22/11/2022
122	22/11/2022
123	30/11/2022	05-Apr-23
124	6/12/2022	09-Dec-22
125	8/12/2022	08-Jun-23
126	8/12/2022	17-Jan-23
127	14/12/2022	16-Dec-22
128	14/12/2022	14-Dec-22
129	16/12/2022
130	16/12/2022	22-Dec-22
131	19/12/2022	15-Aug-23
132	20/12/2022	03-Feb-23

[Mark_Berry]

I expect the above visualisation to show the count of references to show how many were opened each month and how many were closed each month. 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Mark_Berry]

Thanks

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[amustafa]

It seems like you're trying to count distinct dates by month and year in Power BI, but you're getting incorrect counts. This could be due to the way Power BI handles duplicate dates within the same month.

Here's a possible solution:

1. Create a new column that combines the year and month. This can be done using the `MONTH` and `YEAR` functions in Power BI.

YearMonth = YEAR(Table[Date]) & "-" & MONTH(Table[Date])

2. Then, you can use the `CALCULATE` and `DISTINCTCOUNT` functions along with `ALLEXCEPT` to count the distinct dates for each month.

CountOfEnquiries = CALCULATE(DISTINCTCOUNT(Table[Date]), ALLEXCEPT(Table, Table[YearMonth]))

This measure will count the distinct dates for each `YearMonth`, ignoring any filters on other columns in the table.

[Mark_Berry]

Unfortunately this hasn't worked for both columns I am trying to count for.
For some reason the following results are occuring.
I have Only the correct count of Date Opened but not Date closed. I recieve a result of 0 rather than the 8 closures. 
If I use only Date Closed on my Y axis, I don't get a result for the latest closures of which there are 8. 
Opened and Closed are equal across all months, which is not accurate.