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

Kedar_Pande's avatar
Kedar_Pande
Super User
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