Forum Discussion

Bu__'s avatar
Bu__
Frequent Visitor
1 year ago
Solved

identifying active users every month

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 p...
@help
@power bi desktop
@PowerBI Report. @dax
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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.