I would like to display activity counts by year e.g.
| Year | Created | Approved | Resolved |
|---|---|---|---|
| 2017 | 10 | 5 | 19 |
the source records look like this:
| Record ID | Created Date | Approved Date | Resolved Date |
|---|---|---|---|
| 123456 | 9/17/2017 | 1/15/2018 | 11/1/2019 |
Using the example record the report for 2017 should look like:
| Year | Created | Approved | Resolved |
|---|---|---|---|
| 2017 | 1 | 0 | 0 |
I've linked the source table to a Calendar using the record Created Date. When I try to count any other status but Created, however, I get the count of records based on when they were created, and not when the event occurred:
| Year | Created | Approved | Resolved |
|---|---|---|---|
| 2017 | 1 | 1 | 1 |
To count Date Resolved I've used: =calculate(count('source'[Resolved Date]),'calendar'[date]), which I know is incorrect. I want the count to be the number of items occurring during the year specified. Any help would be appreciated!
CodePudding user response：
Sounds like you are looking for BI solution, but in case anyone ever needs a Powerquery/M solution
right click record_id and unpivot other columns
right click value column and tranform year...year
right click remove record_id column
add column, custom column, with formula =1
click attribute column and transform...pivot .. using the new custom column as value column
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", Int64.Type}, {"Created Date", type date}, {"Approved Date", type date}, {"Resolved Date", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record ID"}, "Attribute", "Value"),
#"Extracted Year" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Date.Year, Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Year",{"Record ID"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each 1),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Custom", List.Sum)
in #"Pivoted Column"
