Forum Discussion

rkgundabhat's avatar
rkgundabhat
Helper I
2 years ago
Solved

Filter data

Hello ,  My table has these columns CaseNumber ,Department, CaseOpenedDate, CaseTerminatedDate, CaseCloseDate. I want to know cases which were open in a given month. My example query for the cases w...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    rkgundabhat 

    Please try this measure:

    Open Cases = 
CALCULATE(
    COUNTROWS('CaseTable'),
    FILTER(
        'CaseTable',
        'CaseTable'[CaseOpenedDate] <= MAX('Dates'[Date]) &&
        (
            'CaseTable'[CaseTerminatedDate] = BLANK() ||
            'CaseTable'[CaseTerminatedDate] >= MIN('Dates'[Date])
        ) &&
        (
            'CaseTable'[CaseCloseDate] = BLANK() ||
            'CaseTable'[CaseCloseDate] >= MIN('Dates'[Date])
        )
    )
)
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

rkgundabhat's avatar
rkgundabhat
Helper I
2 years ago
CaseNumberDepartmentCaseOpenedDateCaseTerminatedDateCaseCloseDate
1231HR6/2/20216/2/20226/3/2022
1232Accounts9/15/20226/2/202310/2/2023
1233Facility2/23/2023 6/2/2023
1234IT2/1/201910/2/202211/2/2022

The request is "Show all the cases active in a given month for a department" . 

I figured that I need to use the above query to get my the result which would be cases 1232 and 1234. 

In my report canvas I have filter for department so if I select IT then my count should be 1. 

I am not able to understand what filter do I need to select a year/month in the canvas to get the count for a  department and how to use the query.