Forum Discussion

capricornucopia's avatar
capricornucopia
Frequent Visitor
8 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) ...
Need Help
  • mizan2390's avatar
    mizan2390
    8 months ago

    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
    )

mizan2390's avatar
mizan2390
Icon for Super User rankSuper User
8 months ago

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's avatar
capricornucopia
Frequent Visitor
8 months ago

Thank you so much, that looks to have worked!