identifying active users every month

[Bu__]

Hi,

I have a list of users every month, and i need to find the active users in every month. maybe by creating a new calculated column which states if the user is active or not

- If the user names present in the next available month file then those user names are active 

or

- if a new user appears for the very first time which are not available in any of the month files then they are active from that month onwards.

Please note if user names not appearing continuously then they are inactive.

please find the data like below:

column names are:

System user name,Count of System user name,Sum of cost,Monthly Source file Name

System user name	Count of System user name	Sum of cost	Monthly Source file Name
User_A	3	€ 112.50	24-Jan
User_A	3	€ 112.50	24-Mar
User_A	3	€ 112.50	24-Apr
User_A	3	€ 112.50	24-May
User_A	3	€ 112.50	24-Jun
User_A	3	€ 112.50	24-Sep
User_B	7	€ 149.50	24-Mar
User_B	7	€ 149.50	24-Apr
User_B	7	€ 149.50	24-May
User_B	7	€ 149.50	24-Jun
User_B	8	€ 238.50	24-Sep
User_C	5	€ 139.50	24-Jan
User_C	7	€ 151.50	24-Mar
User_C	7	€ 151.50	24-Apr
User_C	7	€ 151.50	24-May
User_C	7	€ 151.50	24-Jun
User_C	7	€ 151.50	24-Sep
User_D	12	€ 323.00	24-Jan
User_D	13	€ 329.00	24-Mar
User_D	13	€ 329.00	24-Apr
User_D	13	€ 329.00	24-May
User_D	13	€ 329.00	24-Jun
User_D	14	€ 332.00	24-Sep
User_E	5	€ 138.50	24-Jan
User_E	7	€ 209.50	24-Mar
User_E	7	€ 209.50	24-Apr
User_E	9	€ 222.50	24-May
User_E	9	€ 222.50	24-Jun
User_E	9	€ 305.00	24-Sep
User_F	11	€ 362.50	24-Jan
User_F	13	€ 374.50	24-Mar
User_F	14	€ 380.50	24-Apr
User_F	14	€ 380.50	24-May
User_F	14	€ 380.50	24-Jun
User_F	14	€ 463.50	24-Sep
User_G	6	€ 219.50	24-Apr
User_G	7	€ 226.00	24-May
User_G	6	€ 219.50	24-Jun
User_G	7	€ 246.50	24-Sep
User_H	3	€ 112.50	24-Mar
User_H	3	€ 112.50	24-Apr
User_H	3	€ 112.50	24-May
User_H	3	€ 112.50	24-Jun
User_H	3	€ 112.50	24-Sep

How do i create  this Active users list every month using the above data in power bi??

Thanks in advance for the help

[Kedar_Pande · Accepted solution]

@Bu__ 

Create a Calculated Column

User Status = 
VAR CurrentUser = 'YourTable'[System user name]
VAR CurrentMonth = 'YourTable'[Monthly Source file Name]
VAR NextMonth = 
CALCULATE(
MIN('YourTable'[Monthly Source file Name]), 
FILTER(
'YourTable', 
'YourTable'[System user name] = CurrentUser && 'YourTable'[Monthly Source file Name] > CurrentMonth
)
)
VAR PreviousMonths = 
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[System user name] = CurrentUser && 'YourTable'[Monthly Source file Name] < CurrentMonth
)
)
VAR IsFirstTime = PreviousMonths = 0

RETURN
IF (
IsFirstTime, 
"Active", 
IF (
NOT ISBLANK(NextMonth),
"Active",
"Inactive"
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bhanu_gautam]

@Bu__ , 

Create a Calculated Column for Active Status:

Go to the "Modeling" tab and select "New Column".
Use the following DAX formula to create a calculated column that checks if a user is active in the next month:

Active Status =
VAR CurrentMonth = 'Table'[Monthly Source file Name]
VAR CurrentUser = 'Table'[System user name]
VAR NextMonth = CALCULATE(MIN('Table'[Monthly Source file Name]), 'Table'[Monthly Source file Name] > CurrentMonth)
RETURN
IF(
ISBLANK(NextMonth),
"Inactive",
IF(
COUNTROWS(
FILTER(
'Table',
'Table'[System user name] = CurrentUser &&
'Table'[Monthly Source file Name] = NextMonth
)
) > 0,
"Active",
"Inactive"
)
)

Then  Use the following DAX formula to determine if a user appears for the first time:
First Appearance =
VAR CurrentUser = 'Table'[System user name]
VAR FirstMonth = CALCULATE(MIN('Table'[Monthly Source file Name]), 'Table'[System user name] = CurrentUser)
RETURN
IF(
'Table'[Monthly Source file Name] = FirstMonth,
"Active",
BLANK()
)

Then Combine the Active Status and First Appearance:
Create another calculated column to combine the results of the previous two columns:
Final Active Status =
IF(
'Table'[First Appearance] = "Active",
"Active",
'Table'[Active Status]
)

Create a Visual to Display Active Users:
Use a table or matrix visual to display the System user name, Monthly Source file Name, and Final Active Status.
You can also use filters to show only active users for each month.