cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count of users at each status at a specific date

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!

2 ACCEPTED SOLUTIONS
Memorable Member

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:
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
Community Support

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 _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.

12 REPLIES 12
Community Support

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 _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.

Frequent Visitor

Anyone has an idea to solve this please? Thanks!

Super User

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.

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

Frequent Visitor

@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!

Frequent Visitor

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 😞

Memorable Member

@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
Frequent Visitor

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
Memorable Member

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:
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
Frequent Visitor

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.

Memorable Member

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

BBF

Frequent Visitor

@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

Memorable Member

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors