Running Distinct Sum by Month with Condition

I have a dataset like the screenshot below. This dataset shows the training topics, session date, and participant name. Column C target_audience field shows if the participant is a target audience.

Screenshot 2023-05-21 at 9.35.52 PM

I wanted to calculate the cumulative distinct running sum of target audience. I have tried below calculated field but the result is wrong

The result I get is below. I have highlighted the desired result in column G.

For example, for topic food, there are 4 distinct eligible participants (Tom, Alice, James, Susan). Alice and Susan are target audience. Alice joined the session in Jan and Susan joined in April. So, the cumulative distinct running total for target audience by month is 1 for Jan (Alice); no target audience joined in Feb/Mar, so the cumulative total is still 1 for Feb and Mar; and 2 for Apr (Alice+Susan).

I know one way is to remove the {target_audience}=1 field in the calculation and add this as a filter, but I want to add other calculation fields in the same table so I would like to include {target_audience}=1 in the calculated field, instead of as a filter.

Is there any way to calculate it in QuickSight?

Thank you!

Hi @kiko

Can you please try this?

[{session_date} ASC],



Hi @Ramon_Lopez It works. Thank you!

1 Like

Thanks for letting us know and for marking this as solved @kiko!