Forum Discussion

capricornucopia's avatar
capricornucopia
Frequent Visitor
7 months ago
Solved

Create flag column for when category has new date

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!

  • Assuming your table is called FactSpend with columns:  Date,  Audience,  AdvertisementSpend
    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
    )

2 Replies

  • Assuming your table is called FactSpend with columns:  Date,  Audience,  AdvertisementSpend
    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
    )