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 |