Count open cases along with the amount associated with the open cases

[Phoenix12]

Hello, 

I'm pretty new to Power BI and I'm trying to create a report with the total open cases for the month and total amount associated to the cases remaining open. Can someone help with me figure out how I can accomplish this? 

Table 

ID	Created Date	Closed Date	Status	Amount
1	10/1/2022	11/2/2022	Closed	$10
2	10/5/2022	10/25/2022	Closed	$5
3	10/8/2022		Open	$10
4	10/20/2022	10/25/2022	Closed	$13
5	10/31/2022		Open	$10
6	11/1/2022	11/30/2022	Closed	$25
7	11/12/2022		Open	$15
8	11/15/2022	11/26/2022	Closed	$3
9	11/20/2022		Open	$10
10	11/28/2022		Open	$5
11	12/2/2022	12/10/2022	Closed	$6
12	12/5/2022			$5
13	12/9/2022		Open	$10
14	12/15/2022		Open	$10
15	12/20/2022	12/28/2022	Closed	$5

Expected Results: 

Month	Open Count	Open Amount
Oct 2022	2	$20
Nov 2022	3	$30
Dec 2022	3	$25

[alannavarro]

Hello, you can paste this in the advance editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJBCsIwEEWvIsFlITOTTmzXPYAHCN3Znajg/cHo/NpIQ4QsPuTx8idJSo5d55g8eyGRd85pzdP1/lwuORyZDm7ukhOj9UuTF93janQwYliBvM6P5VYKe0joj5GD8WpI4IYz2hjlSIH2SkHLE3CpKcEMYLS8prh3ouUIghotmQDVrgfH8ud1ZHuRnLkySgQORAtjaQu2PbZa9ThFW5CiFhW9tjl+/8H8Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Created Date" = _t, #"Closed Date" = _t, Status = _t, #"Amount " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created Date", type date}, {"Amount ", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Status] = "Open")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Created Date", "Amount "}),
#"Inserted Month Name" = Table.AddColumn(#"Removed Other Columns", "Month Name", each Text.Start(Date.MonthName([Created Date]),3) & " " & Number.ToText(Date.Year([Created Date]))),
#"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Month Name"}, {{"Open Count", each Table.RowCount(_), Int64.Type}, {"Open Amount", each List.Sum([#"Amount "]), type nullable number}})
in
#"Grouped Rows"

[PawelWrona]

Hello, for the number of Open Cases you can go with following formula:

CALCULATE(DISTINCTCOUNT('Table'[ID]), 'Table'[Status] <> "Closed")

Similarly, for value of open cases you would do:

CALCULATE(SUM('Table'[Amount]), 'Table'[Status] <> "Closed")

To represent these values on a month level, would be good to have a Calendar table, linked to your [Created Date] column, so you have additional date values available llike month, quarter and year.

Please, let me know if this is what you needed.

[Phoenix12]

This did work the way it's intended, but I realized the model I need, I additionally need is if the case was closed outside of the month the case was created, then this would be added into the "Open" amount 
For example: October would be $30 due to the one case being closed in November