Forum Discussion

SivaRamaKrishna's avatar
SivaRamaKrishna
New Member
2 years ago

Help needed to create an engagement trend

Hi all,
I am trying to create a chart which should show the engagement percentage trend in a chart. It should take a measure in y-axis for percentage and event_date in x-axis with YearMonth.
The table has user_id, event_date, event_name.
The measure for y-axis is essentially a DIVIDE function with DISTINCTCOUNT of user_id for a specific month in the numerator and the denominator should be DISTINCTCOUNT of user_id till the end of the specified month.
I am able to create the numerator but unable to create the denominator.
#Numerator

 

UsersRunningTotal =
CALCULATE(
    DISTINCTCOUNT('engagement'[user_id]),
    FILTER(
        ALLSELECTED('engagement'[event_date]),
        ISONORAFTER('engagement'[event_date], MAX('engagement'[event_date]), DESC)
    )
)

But, I am unable to create the denominator as it also gives the same result as the numerator by taking identical user counts.
Ultimately the percentage is 100% for every month.
Example for denominator, the mesure should get the DISTINCTCOUNT of user_id from 2018-01-01 to end of March 2024,
from 2018-01-01 to end of April 2024, from 2018-01-01 to end of May 2024 and so on...

My failed attempt for denominator is,
TotalU =
VAR StartDate = DATE(2018, 1, 1)
RETURN
CALCULATE(
    DISTINCTCOUNT(engagement[user_id]),
    FILTER(engagement,
engagement[event_date] > StartDate
    engagement[event_date] <= Date(2024, 05, 31)
    )
)

Please help on how to achieve this.
active users trend
engagement trend

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi SivaRamaKrishna ,

     

    Did some_bih's answer help you? Have you tried his syntax?

     

    If it helped you, please accept it as the solution. This will be of great help to other users who have similar problems as you.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi SivaRamaKrishna it is not easy to propose possible solution without model and other details.

    Still, try v2 below

    TotalU_v2 =
    VAR StartDate = DATE(2018, 1, 1)
    RETURN
    CALCULATE(
    DISTINCTCOUNT(engagement[user_id]),
    FILTER(engagement,
    engagement[event_date] > StartDate &&
    engagement[event_date] <= Date(2024, 05, 31)
    )
    )