Create flag column for when category has new date

[capricornucopia]

I have a table containing date, audience, advertisement, and spend associated with the audience or ad. I'm looking for a way to flag whether there is a new date for a category (i.e., audience or ad) that begins spending within the last week. For example, if the prospect audience begins spending (spend > 0) on 1/5/26, I want the flag column to return a 1 for the prospect audience because it began spending within the last week (12/31/25 - 1/6/26). If its spend began instead on 12/1/25, then it will return a 0 because it is longer than a week ago. Thanks in advance for the help!

[mizan2390 · Accepted solution]

Assuming your table is called FactSpend with columns:  Date,  Audience,  Advertisement, Spend
If the category is Audience: Try
FirstSpendDate_Audience =
CALCULATE (
MIN ( FactSpend[Date] ),
FILTER (
ALL ( FactSpend ),
FactSpend[Audience] = EARLIER ( FactSpend[Audience] )
&& FactSpend[Spend] > 0
)
)

 Flag if first spend is within last 7 days

NewAudienceFlag_Last7Days =
VAR FirstDate =
[FirstSpendDate_Audience]
VAR TodayDate =
TODAY ()
RETURN
IF (
NOT ISBLANK ( FirstDate )
&& FirstDate >= TodayDate - 7
&& FirstDate <= TodayDate,
1,
0
)

[capricornucopia]

Thank you so much, that looks to have worked!