Forum Discussion

molden's avatar
molden
Frequent Visitor
8 years ago
Solved

Check Orders Within A Given Date

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's avatar
    Eric_Zhang
    8 years ago

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

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

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