Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YellowFalsh44
New Member

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

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 MonthREPORT_DATEUNIQUE_ID
2023 February2023/02/011005749180317
2023 February2023/02/014655959445590
2023 February2023/02/014655959445897
2023 February2023/02/011017363709676
2023 February2023/02/011218868736510
2023 February2023/02/013387008658371
2023 February2023/02/013423128237554
2023 January2023/01/011017363709676
2023 January2023/01/019017363709674
2023 January2023/01/019017363709675
2023 January2023/01/019017363709676
2023 January2023/01/013387008658371
2022 December2022/12/011017363709676
2022 December2022/12/013387008658371
2022 December2022/12/013423128237554
2022 December2022/12/015873128237666
2022 December2022/12/015873128237667
2022 November2022/11/011005749180317
2022 November2022/11/011017363709676
2022 November2022/11/011218868736510
2022 November2022/11/013387008658371
2022 November2022/11/013423128237554
2022 November2022/11/013423128237555
2022 November2022/11/013423128237556




Expected Result:

Year and MonthNew CasesOpen CasesClosed Cases
2023 March  6
2023 February433
2023 January323
2022 December234
2022 November7  

 

 

Thank you.

1 ACCEPTED SOLUTION
YellowFalsh44
New Member

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')
)



View solution in original post

2 REPLIES 2
YellowFalsh44
New Member

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
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.