topic Re: Count distinct cases between 2 measures in DAX Commands and Tips

Count distinct cases between 2 measures

Anonymous — Thu, 18 Jun 2020 12:22:22 GMT

I am working on a measure to calculate when a support case is closed and when it is closed within a fiscal month. Below I have an example of when a case was opened before the fiscal month and closed within that month. A case that was opened and closed within the fiscal month and lastly when a case was opened in the fiscal month but closed after the end. My 'Total Fiscal Received' column is marking them as 1 but the total is wrong. This should be 3 rather than 2. I have the formula for this to be: Total Fiscal Resolved = if([Fiscal Resolved]= BLANK(),[Resolved],[Fiscal Resolved])

Fiscal Resolved = CALCULATE(COUNTA(SF_Cases[Closed Date ONLY]),FILTER(SF_Cases,SF_Cases[Created Date ONLY]RELATED(FiscalCalendar_Lookup[Start of Fiscal Month])))

Resolved = CALCULATE(COUNTA(SF_Cases[Closed Date ONLY]),USERELATIONSHIP(FiscalCalendar_Lookup[Date],SF_Cases[Closed Date ONLY]))

I am wondering if anyone has an idea of how I can get this measure to calculate properly.

Period-Year	Case Number	Created Date Only	Closed Date Only	Fiscal Resolved	Start Of Fiscal Month	End of Fiscal Month	Resolved	Count of Case Number	Total Fiscal Resolved
Apr-2019	686725	3/29/2019	4/1/2019		4/1/2019	4/28/2019	1		1
Apr-2019	725507	4/25/2019	4/29/2019	1	4/1/2019	4/28/2019		1	1
Apr-2019	728032	4/26/2019	4/26/2019	1	4/1/2019	4/28/2019	1	1	1
TOTAL	N/A	N/A	N/A	2	N/A	N/A	2	2	2

Re: Count distinct cases between 2 measures

lbendlin — Thu, 18 Jun 2020 00:24:29 GMT

your sample data is inconstent (especially the last close date)

Does it matter how long a case was open? What if it is open for more than one month? More than 3 months?

Re: Count distinct cases between 2 measures

Anonymous — Thu, 18 Jun 2020 12:28:40 GMT

I apologize. I was not able to post a photo of my table and had to manually create the results. I have updated the table to reflect the accurate information. As to your question, it does not matter how long a case is open. There are times a case can be open for 1 year then closed and that case should be counted in that specific fiscal period since it was closed. Which it currently is being counted in my resolved column since it was open before the specific fiscal period, in this case Apr-2019. This type of example is the first row. The case was opened before the fiscal period but closed during that period. My Resolved measure is calculating that correctly but it does not seem to be getting counted in the Total Fiscal Resolved of cases closed or open during that fiscal month.