Hi,
My customer has 4 marketing campaigns per year with the following dates:
- January 1st through April 30th
- May 1st through June 30th
- July 1st through September 30th
- October 1st through December 31st.
So to illustrate my current problem, I created an example Campaigns report with 2 dates tables:
CampaignCalendar = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))
Transactions = CALENDAR(DATE(2018,01,01),DATE(2019,07,31))
And inside CampaignCalendar, I created these addition columns:
CampaignStart =
SWITCH( MONTH([Date]),
1, DATEVALUE(YEAR([Date])&"-01-01"),
2, DATEVALUE(YEAR([Date])&"-01-01"),
3, DATEVALUE(YEAR([Date])&"-01-01"),
4, DATEVALUE(YEAR([Date])&"-01-01"),
5, DATEVALUE(YEAR([Date])&"-05-01"),
6, DATEVALUE(YEAR([Date])&"-05-01"),
7, DATEVALUE(YEAR([Date])&"-07-01"),
8, DATEVALUE(YEAR([Date])&"-07-01"),
9, DATEVALUE(YEAR([Date])&"-07-01"),
10, DATEVALUE(YEAR([Date])&"-10-01"),
11, DATEVALUE(YEAR([Date])&"-10-01"),
12, DATEVALUE(YEAR([Date])&"-10-01")
)
CampaignEnd =
SWITCH( MONTH([Date]),
1, DATEVALUE(YEAR([Date])&"-04-30"),
2, DATEVALUE(YEAR([Date])&"-04-30"),
3, DATEVALUE(YEAR([Date])&"-04-30"),
4, DATEVALUE(YEAR([Date])&"-04-30"),
5, DATEVALUE(YEAR([Date])&"-06-30"),
6, DATEVALUE(YEAR([Date])&"-06-30"),
7, DATEVALUE(YEAR([Date])&"-09-30"),
8, DATEVALUE(YEAR([Date])&"-09-30"),
9, DATEVALUE(YEAR([Date])&"-09-30"),
10, DATEVALUE(YEAR([Date])&"-12-31"),
11, DATEVALUE(YEAR([Date])&"-12-31"),
12, DATEVALUE(YEAR([Date])&"-12-31")
)
LstTx after CampaignStart = MAX('Transactions'[Date]) >= [CampaignStart]
LstTx before CampaignEnd = MAX('Transactions'[Date]) <= [CampaignEnd]
And finally, I created the following DAX code to generate text labels for the Campaign Choice column:
Campaign Choice =
IF(
[CampaignEnd] <= MAX('Transactions'[Date]), --Campaign End Date for current date precedes Last Transaction Date
IF(
AND( CampaignCalendar[LstTx after CampaignStart], --Campaign Start Date for current date precedes Last Transaction Date
CampaignCalendar[LstTx before CampaignEnd] --Campaign End Date for current date greater than Last Transaction Date
),
"Current Campaign", -- Last Transaction Date is between Start and End Campaign Dates of current one (YET NOT OUTPUT WHEN EXPECTED)
"( " & FORMAT([CampaignStart],"yyyy-MM-dd") & " - " & FORMAT([CampaignEnd],"yyyy-MM-dd") & " )"
),
BLANK() --Campaign End Date for current date greater than Last Transaction Date ==> DO NOT OFFER THIS CAMPAIGN YET
)
My problem is that I am not getting “Current Campaign” for dates between July 1st and September 30th even though the columns “LstTx after CampaignStart” and “LstTx before CampaignEnd” are BOTH TRUE. For the other rows, I am getting what I need (either blank for future campaigns, or Start and End Dates for past campaigns).
Here’s a link to a screenshot showing what I’m getting:
https://1drv.ms/u/s!ArclD4PLnaKAnw7U7v6pc1eI70SQ?e=7eWZUk
Here’s link to my report:
https://1drv.ms/u/s!ArclD4PLnaKAnw7U7v6pc1eI70SQ?e=7eWZUk
Any idea what I’m doing wrong or missing?
Thanks,
MT
