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 d...
  • 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]
)
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]
)