topic Re: Counting most recent status at a point in time in DAX Commands and Tips

Counting most recent status at a point in time

Favourity — Wed, 26 Jun 2024 03:32:11 GMT

I am trying to get the status for the customer application as the same time one year ago.
Customers' application status flows through various statuses till they get either approved or declined. The statuses are given positions in descending order with dates tracking to track when each status changed. For example, when a customer applies on January 1, 2023, she/he has a status name "interest" with position 1 and on January 5 if the application is reviewed the status changes to "under-review" with that now positioned as 1, and the initial status of "interest" now 2. How is it possible to count the actual status of a customer at a point in time without counting them as duplicates

Re: Counting most recent status at a point in time

some_bih — Wed, 26 Jun 2024 06:22:08 GMT

Hi Favourity your case seems not something problematic.

To provide possible solution, please share sample data / file with inputs and expected output.

Re: Counting most recent status at a point in time

Favourity — Thu, 27 Jun 2024 05:28:10 GMT

Here is a sample data. The application_ID once and reflect the most current status as at point in time. For example Applixation_ID 11502877 in June 2023 the most current status was wait listed. I do like to automate the count with a DateDim Table

Re: Counting most recent status at a point in time

Anonymous — Fri, 28 Jun 2024 07:49:11 GMT

some_bih Thanks for your contribution on this thread.

Hi Favourity ,

Thanks for providing the sample data. I'm not clear about your requirement. Do you want to get the latest status for per application? If yes, you can create a measure as below to get it:

Measure = VAR _appid = SELECTEDVALUE ( 'Table'[APPLICATIONS_ID] ) VAR _maxid = CALCULATE ( MAX ( 'Table'[POS] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[APPLICATIONS_ID] = _appid ) ) RETURN CALCULATE ( MAX ( 'Table'[APPL_STATUS] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[APPLICATIONS_ID] = _appid && 'Table'[POS] = _maxid ) )

Otherwise, please shared the expected result with the specific examples and calculation logic base on your provided sample data. If it is possible, could you please provide the sample data with Text format?

Best Regards

Re: Counting most recent status at a point in time

Favourity — Mon, 01 Jul 2024 07:17:49 GMT

APPLICATIONS_ID	POS	APPL_STATUS	APPL_STATUS_DESC	APPL_STATUS_DATE	APPL_STATUS_TIME
11502877	1	CANND	Deposit Fee Not Received	2023-09-22	01:32.0
11502877	2	OFFER	Offer Issued	2023-08-19	56:05.0
11502877	3	WAIT	Wait Listed	2023-05-29	22:03.0
11502877	4	WEBAP	Web Application	2023-04-28	45:13.0
11502977	1	CANCI	Cancelled - College Initiated	2022-01-28	35:00.0
11502977	2	WEBAP	Web Application	2021-04-28	45:15.0
1150377	1	CANSP	Cancelled Study Permit Denied	2022-07-22	18:53.0
1150377	2	OFFER	Offer Issued	2021-10-18	50:38.0
1150377	3	INCA	International Cred Assessment	2021-09-20	24:34.0
1150377	4	APPL	Applied	2021-09-20	44:08.0
11503077	1	CANND	Deposit Fee Not Received	2024-06-21	16:07.0
11503077	2	OFFER	Offer Issued	2024-06-18	31:25.0
11503077	3	WAIT	Wait Listed	2024-05-27	13:54.0
11503077	4	WEBAP	Web Application	2024-04-28	45:18.0

Re: Counting most recent status at a point in time

Favourity — Mon, 01 Jul 2024 07:38:32 GMT

Thank you for attempting. Above is a sample data in text format.
To clarify the requirement: I am looking for how to return the post-recent status for each application as of a year ago. For example, if an application was made on January 2023 with status:APPL, POS: 1 and as at June 01 2023 it is in the status: OFFER POS:1. In 2024 June Ido like to know the status the application was in June 2023. I hope I explained the requirement better.
If you prefer I do a video that would be fine or we connet on zoom.

Re: Counting most recent status at a point in time

Anonymous — Mon, 01 Jul 2024 09:47:25 GMT

Hi Favourity ,

You can create a measure as below to get it:

Recent status = VAR _appid = SELECTEDVALUE ( 'Table'[APPLICATIONS_ID] ) VAR _date = SELECTEDVALUE ( 'Table'[APPL_STATUS_DATE] ) //today() RETURN CALCULATE ( MAX ( 'Table'[APPL_STATUS] ), FILTER ( ALL ( 'Table' ), 'Table'[APPLICATIONS_ID] = _appid && YEAR ( 'Table'[APPL_STATUS_DATE] ) = YEAR ( _date ) - 1 && MONTH ( 'Table'[APPL_STATUS_DATE] ) = MONTH ( _date ) ) )

If the above one can't help you figure out, please provide your expected result with backend logic and special examples base on your provided sample data. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Counting most recent status at a point in time

Favourity — Tue, 09 Jul 2024 05:14:38 GMT

Anonymous some_bih
Thank you for your response. To better desribe the data and question. I have added a table here and the expected outcome.
I am looking to count the status of application IDs a year ago using 2024-06-01 reference date. So the logic are:
1. Inception to current date minus one year
2. filter based on count of Minimum value of ranking grouped by Status

1001	1	A	2023-10-05
1001	2	B	2023-05-28
1001	3	C	2023-04-30
1001	4	D	2023-04-12
1001	5	E	2023-01-11
1005	1	A	2024-01-01
1005	2	B	2023-11-28
1005	3	C	2023-05-30
1005	4	D	2023-05-15
1005	5	E	2023-04-30
1005	6	F	2023-04-12
1005	7	G	2023-02-28
1002	1	A	2023-11-30
1002	2	B	2023-05-12
1002	3	C	2023-04-30
1002	4	D	2023-04-12
1002	5	E	2023-01-11
1003	1	A	2024-01-01
1003	2	B	2023-11-28
1003	3	C	2023-05-30
1003	4	D	2023-05-15
1003	5	E	2023-04-30
1003	6	F	2023-04-12
1004	1	A	2023-04-30
1004	2	B	2023-01-11
1004	3	C	2022-12-28

Expected Result

Status	A	B	C
Count	1	2	2

Reference Date: 2024-06-01

Logic needed

Inception to current date minus one year
filter based on count of Minimum value of ranking grouped by Status