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 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 nameCount of System user nameSum of costMonthly Source file Name
User_A3€ 112.5024-Jan
User_A3€ 112.5024-Mar
User_A3€ 112.5024-Apr
User_A3€ 112.5024-May
User_A3€ 112.5024-Jun
User_A3€ 112.5024-Sep
User_B7€ 149.5024-Mar
User_B7€ 149.5024-Apr
User_B7€ 149.5024-May
User_B7€ 149.5024-Jun
User_B8€ 238.5024-Sep
User_C5€ 139.5024-Jan
User_C7€ 151.5024-Mar
User_C7€ 151.5024-Apr
User_C7€ 151.5024-May
User_C7€ 151.5024-Jun
User_C7€ 151.5024-Sep
User_D12€ 323.0024-Jan
User_D13€ 329.0024-Mar
User_D13€ 329.0024-Apr
User_D13€ 329.0024-May
User_D13€ 329.0024-Jun
User_D14€ 332.0024-Sep
User_E5€ 138.5024-Jan
User_E7€ 209.5024-Mar
User_E7€ 209.5024-Apr
User_E9€ 222.5024-May
User_E9€ 222.5024-Jun
User_E9€ 305.0024-Sep
User_F11€ 362.5024-Jan
User_F13€ 374.5024-Mar
User_F14€ 380.5024-Apr
User_F14€ 380.5024-May
User_F14€ 380.5024-Jun
User_F14€ 463.5024-Sep
User_G6€ 219.5024-Apr
User_G7€ 226.0024-May
User_G6€ 219.5024-Jun
User_G7€ 246.5024-Sep
User_H3€ 112.5024-Mar
User_H3€ 112.5024-Apr
User_H3€ 112.5024-May
User_H3€ 112.5024-Jun
User_H3€ 112.5024-Sep

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

Thanks in advance for the help

@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

2 Replies

  • 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

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