Count of the last statuses for item

[Anonymous]

Hello friends,

I have a transactional system where each trx can change status of the client. The status can be changed several times a day for the client. If there are no any trx for several days - client keeps his last status. For example:

Tx_id	Client_id	Date_time	Status
1	CL_1	"2020-09-01 09:00:00"	New
2	CL_2	"2020-09-01 10:00:00"	New
3	CL_1	"2020-09-01 22:00:00"	Perspective
4	CL_2	"2020-09-02 10:00:00"	Perspective
5	CL_3	"2020-09-02 12:00:00"	New
6	CL_3	"2020-09-03 15:00:00"	Perspective
7	CL_1	"2020-09-03 22:00:00"	VIP
8	CL_3	"2020-09-03 23:00:00"	VIP

Expected result: number of clients for each status at the end of day:

Date	New	Perspective	VIP
"2020-09-01"	1	1	0
"2020-09-02"	1	2	0
"2020-09-03"	0	1	2

2020-09-01: Client_1 is in Perspective, because it is his last status at the end of day, Client_2 - in New;

2020-09-02: Client_1 is in Perspective, he did not make any trx but he kept his status from the past, Client_2 - in Perspective; Client_3 - in New;

2020-09-03: Client_1 is in VIP, Client_2 - in Perspective, he kept his status from the past; Client_1 - in VIP.

Can anyone suggest DAX formula to achieve the result?

[stevedep · Accepted solution]

Hi,

The code below does the trick:

__Counttrx = 
var _selDate = CALCULATE(MAX('Table'[Date_time]), ALL('Table'[Client_id]), ALL('Table'[Status]))
var _selStatus = SELECTEDVALUE('Table'[Status])
var _tblClients = CALCULATETABLE(
                        SUMMARIZE(ALL('Table'),'Table'[Client_id], "MaxDate", CALCULATE(MAX('Table'[Date_time]),  'Table'[Date_time] <= _selDate)))
var _tblClientsStatus = SUMMARIZE(_tblClients,[Client_id],[MaxDate], "Status", COUNTROWS(FILTER(ALL('Table'),'Table'[Client_id]=[Client_id] && 'Table'[Date_time]=[MaxDate] && 'Table'[Status] = _selStatus)))
return
SUMX(_tblClientsStatus,[Status])

As can be seen here:

Link to the file here.

Please mark as solution if so. Thumbs up for the effort is appreciated.

In this video I explain how I approach such a challenge. It has some editing issues, hopefully, it's still helpful. 

Kind regards,

Steve. 

[Anonymous]

Hi @stevedep ,

it is not good - it is great!!!

Works with test data. I will try to implement on prod too.

Grateful for your help.

[stevedep]

@Anonymous , Welcome!

I also created a video to explain how to approach and tackle such a challenge. It has some editing issues, but I hope it's still helpful. It does require quite some DAX knowledge.