Forum Discussion

terrajen's avatar
terrajen
Frequent Visitor
8 years ago

count with start and end date

I'm fairly new to PowerBi and running into issues figuring out what is probably a simple issue. I'm trying to count the number of records (orders) generated from an email campaign, which has a start and end date. I have the 'Campaign Summary' table, that has a campaignID, customeremail, SendDate and EndDate. THere is also the Order table that contains OrdNbr, customeremail and OrderDate. I would like to get the number of orders generated between an email campaign's start and end dates (CountOrders). The formula only counts records that fall on the start date and not between the Start and EndDate.  Here's what I'm trying:

 

NumberofOrders = calculate(DISTINCTCOUNT(Order[OrdNbr])) 

 

CountOrders = MAXX (FILTER ( 'Campaign Summary',MAX ( Order[OrdDate] ) >= 'Campaign Summary'[SendDate] && MAX ( Order[OrdDate] ) <= 'Campaign Summary'[EndDate]),Order[NumberofOrders])

 

Here's the result: 4/25/2017 is the Start Date for the email campaign but it is not counting the records that fall within the date range of the campaign (in this instance 4/25/2017-5/05/2017)

 

 

pic

 

3 Replies

  • Can you post sample data , seems like these two tables doesn;'t have relationship

    • terrajen's avatar
      terrajen
      Frequent Visitor

      There are other tables in the dataset. The two tables are related via a Dimdate table.