Forum Discussion
Summarize Data by Max Date
Hello!
I have a status transactions table as follows:
| date | id | status |
| 1/1/2019 | A | New |
| 1/15/2019 | A | Open |
| 2/1/2019 | A | Closed |
| 2/15/2019 | B | New |
| 3/1/2019 | B | Open |
| 3/15/2019 | B | Closed |
| 4/1/2019 | A | Stop |
| 4/15/2019 | B | Stop |
I need to summarize IDs by Status over time. For example:
| status | January | February | March | April |
| New | 0 | 1 | 0 | 0 |
| Open | 1 | 0 | 0 | 0 |
| Closed | 0 | 1 | 2 | 0 |
| Stop | 0 | 0 | 0 | 2 |
Conceptually, I need to count the current status for each ID that is associated with the maximum transaction date that is earlier than the end of each time period. I'm able to get the counts by status and filter by date, but selecting only the maximum date for each ID is proving difficult.
Any help on this would be appreciated!
8 Replies
Hi,
For March closed, the answer should be 1. You may download my PBI file from here.
Hope this helps.
Thank you for your response!
To clarify, I'm not just trying to organize raw transactions by status and month. I'm trying to summarize the current status of each ID by month. Since ID A moved to status = Closed in February and status = Stop in April, it is still in status = Closed in March. As such, the count of status = Closed in March should be 2.
Hi,
I have tried hard enough but have not been successful in solving it. If you find a solution, please share it here.
