Count of users at each status at a specific date

[Monepieveloce]

Hello All! 
I have a table with columns username, current status and update date. In this table all the changes of status are recorded.
I need to calculate the count of users at each status and filter it by month, so when then month filter is applied the calculation should show the outcome at the end of that month.
Sample table:

Username	Current Status	Update Date
AAA	1	1/May/24
BBB	1	1/May/24
CCC	2	1/May/24
DDD	3	1/May/24
EEE	4	1/May/24
AAA	2	1/Jun/24
FFF	1	1/Jun/24
BBB	2	1/Jun/24
CCC	2	1/Jun/24
AAA	1	1/Jul/24
GGG	2	1/Jul/24
AAA	4	1/Aug/24

Sample expected result:

May filter applied
Status	# Count
1	2
2	1
3	1
4	1

June filter applied
Status	# Count
1	1
2	3
3	1
4	1

July filter applied
Status	# Count
1	2
2	3
3	1
4	1

August filter applied
Status	# Count
1	1
2	3
3	1
4	2

Anyone can help? 

Thank you!

[BeaBF · Accepted solution]

@Monepieveloce 

1. Create a separate table for Dates, in my case i named it DateSampleTable, with this code:

DateSampleTable = DISTINCT(SampleTable[Update Date])

2. Create the measure:

CountByUsernameStatus =

VAR VirtualTable =

    CALCULATETABLE(SUMMARIZE(FILTER('SampleTable', SampleTable[Update Date] <= SELECTEDVALUE(DateSampleTable[Update Date])), SampleTable[Username], "Date", MAX(SampleTable[Update Date]), "STATUS", MAX(SampleTable[Current Status])))

RETURN

    COUNTROWS(VirtualTable)

Try it and let me know.

BBF

[v-zhouwen-msft · Accepted solution]

Hi all,thanks for the quick reply, I'll add more.

Hi @Monepieveloce ,

Please follow these steps:

1.Deleting a relationship between tables.

2.Use the following DAX expression to create a table

Table = VALUES(UsersTable[Current Status])

3.Use the following DAX expression to create a measure

Measure = 
VAR _dateForSlicer = MIN('DateTable'[Date].[Date])
VAR _status = SELECTEDVALUE('Table'[Current Status])
VAR _table = SUMMARIZE(FILTER('UsersTable',[Update Date] <= _dateForSlicer),[Username],"Update Date",MAX('UsersTable'[Update Date]))
VAR _table2 = ADDCOLUMNS(_table,"Current Status",LOOKUPVALUE(UsersTable[Current Status],UsersTable[Username],[Username],UsersTable[Update Date],[Update Date]))
VAR _result = COUNTROWS(FILTER(_table2,[Current Status] = _status))
RETURN _result

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Monepieveloce]

Anyone has an idea to solve this please? Thanks!

[foodd]

This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[Monepieveloce]

@foodd Thank you for the guidelines! 
I have shared the sample file here: https://we.tl/t-YcGOXa0Qm6 
I have added in the file how I would like to visualize the data (column chart + month slicer)
Thank you in advance for the help! 

[Monepieveloce]

Thanks for looking into the issue! 
I tried the proposed solution, but I got the count of users at each level only for those users who had an update on the specific month, while users that were at the same status from a previous month were not considered 😞

[BeaBF]

@Monepieveloce oh sorry, i didn't undestand. But, based on your example, i can't understand what do you add for each month. can you explain me how do you reach the output of july?

July filter applied
Status	# Count
1	2
2	3
3	1
4	1

[Monepieveloce]

Thanks you for the reply! 
The status of each user in July is the following, so that is why I get the july outout posted above :

User	Status in July	Last change
AAA	1	1/Jul/24
BBB	2	1/Jun/24
CCC	2	1/Jun/24
DDD	3	1/May/24
EEE	4	1/May/24
FFF	1	1/Jun/24
GGG	2	1/Jul/24

[BeaBF · Accepted solution]

@Monepieveloce 

1. Create a separate table for Dates, in my case i named it DateSampleTable, with this code:

DateSampleTable = DISTINCT(SampleTable[Update Date])

2. Create the measure:

CountByUsernameStatus =

VAR VirtualTable =

    CALCULATETABLE(SUMMARIZE(FILTER('SampleTable', SampleTable[Update Date] <= SELECTEDVALUE(DateSampleTable[Update Date])), SampleTable[Username], "Date", MAX(SampleTable[Update Date]), "STATUS", MAX(SampleTable[Current Status])))

RETURN

    COUNTROWS(VirtualTable)

Try it and let me know.

BBF

[Monepieveloce]

@BeaBF thanks for the answer!
Unfortunately it did not work, since the formula counts the users per status but shows only the users that had an update in the month selected in the slicer, so it does not take into consideration what happened before the month selected. 
What I was able to achieve is the cumulative count of users at each month (but I was not able to show the categorization by status), or the count of users at each status who had an update on that month. I could not find a way to combine the 2 and get the cumulative count of users at each status at the end of the month.

[BeaBF]

@Monepieveloce the slicer must be an external table, did you do it?

BBF 

[Monepieveloce]

@BeaBF 
Yes I did so but it did not work, I believe I followed the same steps but apparently I did something wrong 😞

In the meanwhile I created a table printing the status of each user at the end of each month and I got what I need, but I am afraid this table may slow down the performance

[BeaBF]

@Monepieveloce Hi! You don't need any calculation, you have to construct the table in this way:

you'll obtain a table with the Status and the count of Users for each Status and you can easily filter the table by a slicer on month, like:

But, if you want a measure, you can use:

UsersCount = CALCULATE(DISTINCTCOUNT(SampleTable[Username]), ALLEXCEPT(SampleTable, SampleTable[Current Status], SampleTable[Update Date]))

BBF