New Cases, Closed cases and Open cases per month based on previous month

[YellowFalsh44]

Hi All,

Trying to achieve something power bi through measures. I have monthly data with the following columns

Report_date, Unique_ID

I have my date table.

I want to populate the count of new cases, closed cases and open cases based on this data in a table format.

The conditions to be applied are all these cases should be populated based on the comparision of selected month and the previous month of the selected month.

New cases - Not in the previous month and only in the selected month

Closed cases - In the previous month but not in the selected month (These closed cases count should be displayed in the next month in the table i.e., if a case is closed in Jan 2023 then the count of the closed case should be displayed in Feb 2023 closed cases row.)

Open cases - Present both in the both previous month and the selected month

Note : The comparision should always be with selected month and the previous month of the selected month.

I.e., if seleted Feb 2023 then feb 2023 data should be only compared with Jan 2023 Or if seleted Jan 2023 then Jan 2023 data should only be compared with Dec 2022 data and vice versa.

Sample Data:

Year and Month	REPORT_DATE	UNIQUE_ID
2023 February	2023/02/01	1005749180317
2023 February	2023/02/01	4655959445590
2023 February	2023/02/01	4655959445897
2023 February	2023/02/01	1017363709676
2023 February	2023/02/01	1218868736510
2023 February	2023/02/01	3387008658371
2023 February	2023/02/01	3423128237554
2023 January	2023/01/01	1017363709676
2023 January	2023/01/01	9017363709674
2023 January	2023/01/01	9017363709675
2023 January	2023/01/01	9017363709676
2023 January	2023/01/01	3387008658371
2022 December	2022/12/01	1017363709676
2022 December	2022/12/01	3387008658371
2022 December	2022/12/01	3423128237554
2022 December	2022/12/01	5873128237666
2022 December	2022/12/01	5873128237667
2022 November	2022/11/01	1005749180317
2022 November	2022/11/01	1017363709676
2022 November	2022/11/01	1218868736510
2022 November	2022/11/01	3387008658371
2022 November	2022/11/01	3423128237554
2022 November	2022/11/01	3423128237555
2022 November	2022/11/01	3423128237556

Expected Result:

Year and Month	New Cases	Open Cases	Closed Cases
2023 March			6
2023 February	4	3	3
2023 January	3	2	3
2022 December	2	3	4
2022 November	7

Thank you.

[YellowFalsh44 · Accepted solution]

Sorry for the delayed responce

I got a solution that works for me:

New Cases =
VAR SelectedMonth = MONTH(MAX(Calender[Date]))
VAR SelectedYear = YEAR(MAX(Calender[Date]))
VAR PreviousMonth1 = EOMONTH(MAX(Calender[Date]), -1)
VAR PreviousMonthYear = YEAR(PreviousMonth1)
VAR PreviousMonthNumber = MONTH(PreviousMonth1)
RETURN
CALCULATE(
COUNTROWS(FILTER('Table',
MONTH('Table'[ Report Date]) = SelectedMonth &&
YEAR('Table'[ Report Date]) = SelectedYear &&
NOT( 'Table'[UNIQUE_ID] IN CALCULATETABLE(
VALUES('Table'[UNIQUE_ID]),
FILTER('Table',
YEAR('Table'[ Report Date]) = PreviousMonthYear &&
MONTH('Table'[ Report Date]) = PreviousMonthNumber
)
)
))),
ALLSELECTED('Table')

Closed Case =
VAR SelectedMonth = MONTH(MAX(Calender[Date]))
VAR SelectedYear = YEAR(MAX(Calender[Date]))
VAR PreviousMonth1 = EOMONTH(MAX(Calender[Date]), -1)
VAR PreviousMonthYear = YEAR(PreviousMonth1)
VAR PreviousMonthNumber = MONTH(PreviousMonth1)
RETURN
CALCULATE(
COUNTROWS(FILTER('Table',
MONTH('Table'[ Report Date]) = PreviousMonthNumber &&
YEAR('Table'[ Report Date]) = PreviousMonthYear &&
NOT( 'Table'[UNIQUE_ID] IN CALCULATETABLE(
VALUES('Table'[UNIQUE_ID]),
FILTER('Table',
YEAR('Table'[ Report Date]) = SelectedYear &&
MONTH('Table'[ Report Date]) = SelectedMonth
)
)
))),
ALLSELECTED('Table')
)

Open Cases =
VAR SelectedMonth = MONTH(MAX(Calender[Date]))
VAR SelectedYear = YEAR(MAX(Calender[Date]))
VAR PreviousMonth1 = EOMONTH(MAX(Calender[Date]), -1)
VAR PreviousMonthYear = YEAR(PreviousMonth1)
VAR PreviousMonthNumber = MONTH(PreviousMonth1)
RETURN
CALCULATE(
COUNTROWS(FILTER('Table',
MONTH('Table'[Report Date]) = SelectedMonth &&
YEAR('Table'[Report Date]) = SelectedYear &&
'Table'[UNIQUE_ID] IN CALCULATETABLE(
VALUES('Table'[UNIQUE_ID]),
FILTER('Table',
YEAR('Table'[Report Date]) = PreviousMonthYear &&
MONTH('Table'[Report Date]) = PreviousMonthNumber
)
)
)),
ALLSELECTED('Table')
)

[lbendlin]

New and returning customers – DAX Patterns