Filter data

[rkgundabhat]

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 which were open on Sep 2022 would be

Select * from Table where CaseOpenedDate <= '30-Sep-2022' and CaseTerminatedDate is null or CaseTerminatedDate >= '01-sep-2022' and CaseCloseDate is null or CaseCloseDate >= '01-sep-2022' 

In my dashboard I want to be able to select Date filter and Department and get all the cases where were open in any month. 

Need help with this please. 

Thank you

RK

[Ashish_Mathur]

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]

CaseNumber	Department	CaseOpenedDate	CaseTerminatedDate	CaseCloseDate
1231	HR	6/2/2021	6/2/2022	6/3/2022
1232	Accounts	9/15/2022	6/2/2023	10/2/2023
1233	Facility	2/23/2023		6/2/2023
1234	IT	2/1/2019	10/2/2022	11/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. 

[Fowmy · Accepted solution]

@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])
        )
    )
)

[rkgundabhat]

This is working as I wanted. So I created a meansure as you suggested and created dates table from below.  I added this OpenCases measure to my Cases table and used the Dates table as filter. 

Thank you so much Fowmy

[rkgundabhat]

I think from the above DAX code , I only have CaseTable. What is the Dates table and the Date column?

[rkgundabhat]

Working on it

[rkgundabhat]

I am getting an error cannot find the name "Dates"

[Fowmy]

@rkgundabhat 

Create a Dates table with the code that I have given below but no need for a relationship to be made, use the Year Month on a Slicer to select the month:

Dates = 
ADDCOLUMNS(
    CALENDAR("01/01/2019","31/12/2023"),
    "Month No" , MONTH([Date]),
    "Month Name", FORMAT([Date],"Mmmm"),
    "Year",YEAR([Date]),
    "Year Month No", INT(FORMAT([Date],"yyyymm")),
    "Year Month" , FORMAT([Date],"Mmm yyyy"),
    "Quarter No", QUARTER([Date]),
    "Quarter" , FORMAT([Date],"\QQ")
)

[rkgundabhat]

I am getting output without errors. I will QA the data and get back to you . Please bear with me. 

Thank you so much