COUNT DAX with Date Conditions

[superhayan]

Hello all, I am stuck in a COUNT DAX with date conditions and hopefully the experts here can give me a helping hand.

I have a table of compliance cases which only has 4 columns: Case ID, Case Create Date, Case Close Date, Current Status. My ultimate goal is to plot a line chart with Month as x-axis and 2 trend lines showing in each month, how many cases are closed and how many are still in backlog (created but not yet closed in that month). An example of the table is shown below. Sorry that I cannot access to any cloud drive with work computer hence I cannot share any example data.

Compliance Case ID	Case Create Date	Case Close Date	Current Status
1	01/05/2022	01/08/2022	Closed
2	01/11/2022		Waiting for info
3	01/03/2023	01/03/2024	Closed
4	01/04/2023	01/04/2024	Closed
5	01/05/2023		In Progress
6	01/07/2023	01/01/2024	Closed
7	01/10/2023		In Progress
8	01/01/2024		In Progress
9	01/03/2024	01/05/2024	Closed
10	01/04/2024		In Progress
11	01/04/2024		Waiting for info

I have also created a Calendar table and linked it to the Case Close Date with a 1:many relatioship and added a column [Date_Month] to show the mmmyy of each date. I have managed to get the closed cases line with below measure. This measure works well when I pull it as the y-axis trend line with 'Calendar'[Date_Month] as x-axis. 

Count_Closed = CALCULATE(COUNT('Compliance'[Case ID]),'Compliance '[Current Status]= "Closed")

However I don't know how to get the backlog trendline. I believe it is the sum of below 2 measures (but there might be other calculation methods):

1) Count of cases that are created already in each month on the x-axis, Current Status <> Closed 

2) Count of cases that are created already in the each month on the x-axis, Current Status = Closed but not yet closed in that month on x-axis. 

Thanks for helping in advance!

[Anonymous · Accepted solution]

Thanks @JamesFR06  for the quick reply!

Hi @superhayan ,

(1) My test data is the same as yours.

(2) We can create measures.

Count_backlog = COUNTROWS(FILTER(ALLSELECTED('Compliance'),[Case Create Date]<=MAX('DateTable'[Date]) && [Current Status] <> "Closed"))

Count_Closed = CALCULATE(COUNT(Compliance[Compliance Case ID]),FILTER(ALLSELECTED('Compliance'),'Compliance'[Case Close Date]=MAX('DateTable'[Date]) && 'Compliance'[Current Status]="Closed"))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[JamesFR06]

Hi,

Hope this is what you want :

CreatedOC = DISTINCTCOUNT('Table (2)'[Compliance Case ID])

Openprjct =

Var mindate=min('Date'[Date])

Var Maxdate=Max('Date'[Date])

var result=countrows(filter('Table (2)','Table (2)'[Case Create Date]>=Mindate&&'Table (2)'[Case Create Date]<=Maxdate&&'Table (2)'[Current Status]="Closed"&&'Table (2)'[Case Close Date]>Maxdate))

return

result

[superhayan]

Thanks for your reply but sadly it gives a blank line chart. I pulled the formula to a Card visual and it showed "--". I am suspecting its because the x-axis (calendar) is related to Case Close Date and only cases that are "Closed" has a close date, so when we only count NON-Closed cases so it returns nothing?