If over 100% by campaign end date, then 100%, else original value

[minolim]

Hi everyone,

I have a data model with 4 tables:

Media Plan Table

Campaign Name

Campaign ID

Start Date

End Date

Budget Available

New Clients Target

Media Plan Pivot

Campaign ID

Date

Target Spend per Day

Target New Clients per Day

Calendar Table

Date

Year

Month

Day

Quarter

Year Half

Campaign Data

Campaign ID

Date

Actual Spend

New Clients

Using the informatino from Media Plan Pivot and Campaign Data I am able to create the following measure:

Pacing - New Clients = DIVIDE(New Clients, Target New Clients per Day)

This gives me an easy understanding of how my campaign is pacing vs our target for each day by using a time slider. For example:

01/01/2022 - 98% achieved vs target (we are sligthly underpacing)

01/01/2022 to 04/04/2022 - 143% achieved vs target  (we are over pacing vs campaign target)

What I would like to do is the following:

IF Pacing - New Clients is over 100% by the End of that campaign, then put 100% ELSE put the original pacing amount.

Any idea of how to make it happen?

[Anonymous]

Hi @minolim ,

What's the data in the New Clients field and the Target New Clients per Day field?

Numbers or text you want count?

Try to use the SUM() or COUNT() to get the values. Then use the IF() to return different results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.