Forum Discussion

ginalu's avatar
ginalu
Icon for Helper I rankHelper I
2 years ago

How to Link the Growth Rate Calculation with Filters

Why does the ActiveGrowthRate metric not change with the filters?

 

For example, viewing [ActiveGrowthRat]e by different countries.

The formula for [ActiveGrowthRate] consists of the metrics [Recent7DaysActiveUsers] and [Previous7DaysActiveUsers], both of which successfully respond to country filters.

 

The detailed formula is as follows:

 

Recent7DaysActiveUsers =
CALCULATE(
DISTINCTCOUNT(USER[user_id]),
FILTER(
LESSON,
LESSON[start_date] >= TODAY() - 7 && LESSON[start_date] <= TODAY()
)
)

 

Previous7DaysActiveUsers =
CALCULATE(
DISTINCTCOUNT(USER[user_id]),
FILTER(
LESSON,
LESSON[start_date] >= TODAY() - 14 && LESSON[start_date] <= TODAY()-7
)
)

 

ActiveGrowthRate =
DIVIDE(
[Recent7DaysActiveUsers] - [Previous7DaysActiveUsers],
[Previous7DaysActiveUsers],
0
)*100

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi ginalu - create below measure for recent7days active users as below:

     

    Recent7DaysActiveUsers =
    VAR StartDate = TODAY() - 7
    VAR EndDate = TODAY()
    RETURN
    CALCULATE(
    DISTINCTCOUNT(USER[user_id]),
    FILTER(
    LESSON,
    LESSON[start_date] >= StartDate && LESSON[start_date] <= EndDate
    )
    )

    create another measure Previous7DaysActiveUsers :

    Previous7DaysActiveUsers =
    VAR StartDate = TODAY() - 14
    VAR EndDate = TODAY() - 7
    RETURN
    CALCULATE(
    DISTINCTCOUNT(USER[user_id]),
    FILTER(
    LESSON,
    LESSON[start_date] >= StartDate && LESSON[start_date] <= EndDate
    )
    )

     

    For active growth rate measure by using above two measure for 7 and 14 active to get the %

    ActiveGrowthRate =
    VAR RecentUsers = [Recent7DaysActiveUsers]
    VAR PreviousUsers = [Previous7DaysActiveUsers]
    RETURN
    DIVIDE(
    RecentUsers - PreviousUsers,
    PreviousUsers,
    0
    ) 

     

     

    to get the percentage, select the activegrowthrate measure and select the percentage % on your pane header.

     

    Hope it works

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ginalu 

     

    Did rajendraongole1's method solve your problem? If yes, could you please mark it as a solution? This will be of great help to other users experiencing similar problems. Thank you!

     

    If the problem persists, please provide some sample data used in the formula and the expected results based on the sample data so that we can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community  Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu