Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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"
Hello, for the number of Open Cases you can go with following formula:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.