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.
