In this writing, I want to share what I have learned by practicing with marketing campaign data that contains campaign-startdate-column, campaign-enddate-column, and total cost of each campaign.
The first step is to know how long is the duration of each campaign-id.
The second step is to divide the total cost by the duration of each campaign-id. This makes to know how much is the average daily cost per each campaign-id.
The third step is to write DAX measure like below. In the data model, the relationships between the one date-column in the date-table and the two date-related-columns in the marketing-campaign-table, both have to be inactive.
campaign cost total fix =
VAR
campaignbydate =
SUMMARIZE (
dates,
dates[Date],
"campaigncost",
CALCULATE (
SUM ( Marketing_Campaigns[Campaign Daily Cost avg] ),
FILTER (
VALUES ( Marketing_Campaigns[Campaign Start] ),
Marketing_Campaigns[Campaign Start]
<= MAX ( dates[Date] )
),
FILTER (
VALUES ( Marketing_Campaigns[Campaign End] ),
Marketing_Campaigns[Campaign End]
>= MIN ( dates[Date] )
)
)
)
RETURN
SUMX (
campaignbydate,
[campaigncost]
)
Until this point, I could analyze how is the trend in marketing campaign costs.
The fourth step is for creating the connection between the marketing-campaign-table and sales-table.
When seeing the two tables, both of them has the area-related-column.
When seeing the flows of filters in the data model pane, it looks like below.
The reasonable thinking to connect the sales-table and marketing-campaign-table is, in my opinion, if the marketing campaign activity was shown (campaign cost was not zero) in the selected region in the selected dates, show the sales amount. If the marketing campaign activity was not shown (campaign cost was zero) in the selected region on the selected dates, do not show the sales amount.
So, I tried to write the below-calculated measure.
sales total =
CALCULATE (
SUMX (
Sales,
Sales[Unit Price] * Sales[Order Quantity]
),
FILTER (
VALUES ( State_Regions[Region] ),
[campaign cost total fix] > 0
)
)
By showing the sales total amount like above, dimensions in the marketing-campaign-table could filter the sales amount in the sales table.
The sample data is from EnterpriseDNA, and I learned two big takeaways.
Firstly, when the cost-related table has two date columns, try to think whether or not describing a daily-cost calculation is reasonable. If it is, then do it. Until today, I faced many times with a sales-related table that has two or more date-columns, but I did not create calculated columns by showing total sales divided by the duration of dates. In some cases, it might be useful but I did not happen to come up with it. In my opinion, even the relationships in the data model and calculated measures in writing DAX look similar, the thinking process has to be done differently depends on the situation.
Secondly, when the one fact table cannot filter the other fact table, but when there is a common dimension, then try to come up with the idea that,
if one fact table shows zero under the certain criteria in the dimension, then that certain criteria also need to show zero in the other fact table.
I hope this helps to step further without having any huge time delays and difficulties as I had, when facing two or more fact tables with two or more dates columns in one table and those tables need to filter one another.
I publish to web to show my practice like below.
Comments