Check Orders Within A Given Date

[molden]

Hi All,

I'm new to Power BI. For my 1st project I am trying to determine if an order came from a marketing campaign.

I have a list of campaigns, the customers involved and the start and end date they ran from.

What I am trying to figure out is for every order is the customer in a campaign and have ordered between the start and end date the campaign ran for.

Hope this makes sense. 

I have set up 3 spread sheets:

Campaign Audience

Campaign ID

Customer ID

Campaigns

Campaign ID

Campaign Name

Start Date

End Date

Orders

Customer ID

Order Date

Product

Thanks in advance.

Michael

[Eric_Zhang · Accepted solution]

@molden

You can either create a measure as below

1.You can try to create a measure as below. Then you may get the respected result.
Measure =
MAXX (
    FILTER (
        campaigns,
        MAX ( orders[order date] ) >= campaigns[start date]
            && MAX ( orders[order date] ) <= campaigns[end date]
    ),
    campaigns[campaign id ]
) 

Or create an auxiliary table to extend the campaign row(start date and end date) to rows of each day. And then create a one to one relationship between the created table and order table.

Table =
FILTER (
    CROSSJOIN ( 'calendar date', campaigns ),
    'calendar date'[Date] >= campaigns[start date]
        && 'calendar date'[Date] <= campaigns[end date]
)