Summarize Data by Max Date

[emmetk]

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!

[Ashish_Mathur]

Hi,

For March closed, the answer should be 1.  You may download my PBI file from here.

Hope this helps.

[emmetk]

Hi @Ashish_Mathur 

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.

[Ashish_Mathur]

Hi,

I think i have finally solved the problem.  Are you still interested in getting this solution?

[emmetk]

Hi @Ashish_Mathur,

Yes, I am still looking for a solution to this! Thank you for continuing to work on this - anything you are able to share would be appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[emmetk]

Thank you @Ashish_Mathur.

This does solve the problem. However, is it possible to do this without duplicating the input data table for every relevant date? The mock data was only 5 or so records, but your method increased that record count to over 300. Considering my real data consists of thousands of records over many years, this method would not be sustainable in the long run. What do you think?

[Ashish_Mathur]

Hi,

It could be unsustainable.  Only you can try and offer feedback.  This is the only method i know.

[Ashish_Mathur]

Hi,

I have tried hard enough but have not been successful in solving it.  If you find a solution, please share it here.