Check Orders Within A Given Date

molden — Fri, 06 Oct 2017 10:28:16 GMT

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

Re: Check Orders Within A Given Date

Eric_Zhang — Mon, 09 Oct 2017 08:36:58 GMT

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]
)

topic Check Orders Within A Given Date in Developer

Check Orders Within A Given Date

Re: Check Orders Within A Given Date