Forum Discussion
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)
3 Replies
- parry2k
Super User
Can you post sample data , seems like these two tables doesn;'t have relationship
- terrajenFrequent Visitor
There are other tables in the dataset. The two tables are related via a Dimdate table.
- Aron_Moore
Solution Specialist