How to count items at different intervalls

[MartenB]

Hi,

I have three tables:
Sales Header containing [date] and [order no]

Sales Line containg [order no], [item no], [sold quantity] and [item price]

Campaign containing [item no], [campaign start] (date) and [campaign end] (date

How can I list sold quantity per item for their respective campaign period?
Background:

We have a sales campaign that start at the earliest [campaign start] and ends at the latest [campaign end]. During this period different items/products will have their respective campaign with specific pricing and offers (like 3 for 2 etc). I want to be able to show (table and column chart), the quantity sold (and total revenue) per item for the entire sales campaign but only count items during their respective "sub-campaign period". How can I accomplish that. I understand that I need a measure (or maybe a new column). Please advice since I'm a beginner on DAX.

Best regards, Mårten

[Anonymous]

Hi @MartenB ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

quantity sold = CALCULATE(SUM('Sales Line'[sold quantity]),FILTER('Sales Line','Sales Line'[item no]=EARLIER('Campaign'[item no]) && RELATED('Sales Header'[date]) <='Campaign'[campaign end] && RELATED('Sales Header'[date]) >= 'Campaign'[campaign start]))

total revenue = 
var _a=CALCULATE(SUM('Sales Line'[sold quantity]),FILTER('Sales Line','Sales Line'[item no]=EARLIER('Campaign'[item no]) && RELATED('Sales Header'[date]) <='Campaign'[campaign end] && RELATED('Sales Header'[date]) >= 'Campaign'[campaign start]&& 'Sales Line'[order no]=RELATED('Sales Header'[order no])))
var _b=CALCULATE(SUM('Sales Line'[item price]),FILTER('Sales Line','Sales Line'[item no]=EARLIER('Campaign'[item no]) && RELATED('Sales Header'[date]) <='Campaign'[campaign end] && RELATED('Sales Header'[date]) >= 'Campaign'[campaign start]&& 'Sales Line'[order no]=RELATED('Sales Header'[order no])))
return _a*_b

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[123abc]

To calculate the quantity sold per item for their respective campaign period in Power BI using DAX, you can create a new measure. Here's how you can do it step by step:

1. Create a Calendar Table: It's essential to have a calendar table to define the time period for your campaign. You can create one using the "Enter Data" option in Power BI or import it from an external source.

2. Create Relationships: Ensure that you have set up the relationships between your tables. The "Sales Header" table should be related to the "Sales Line" table using the [order no] column, and the "Sales Line" table should be related to the "Campaign" table using the [item no] column.

3. Define a New Measure: In Power BI, go to the Modeling tab and select "New Measure." You can use DAX to create a measure that calculates the quantity sold for each item during its respective campaign period.

   Here's a DAX measure that calculates the quantity sold per item for their respective campaign period:

QuantitySoldDuringCampaign =
CALCULATE(
SUM('Sales Line'[sold quantity]),
FILTER(
'Campaign',
'Sales Header'[date] >= 'Campaign'[campaign start]
&& 'Sales Header'[date] <= 'Campaign'[campaign end]
)
)

1. This measure uses the CALCULATE function to filter the sales data for each item based on the campaign period specified in the 'Campaign' table.

2. Create Visuals: Once you have created the measure, you can use it in your table or column chart visualizations to display the quantity sold per item for their respective campaign periods.

Remember to replace table and column names with your actual table and column names. After creating the measure and setting up your visuals, you should be able to see the quantity sold for each item during their respective campaign periods. You can also create another measure for total revenue by multiplying the quantity sold by the item price if needed.

This should help you accomplish your goal of showing the quantity sold for each item during their campaign periods in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[MartenB]

Thank you for fast reply. I still seem to have some problem though.

-I had already set up a calendar table (with a relationship to 'Sales header'[date]).
-The relationships are set up as you suggested (also tried bidirectional).
When I try to write the DAX code i get the following problem:

QuantitySoldDuringCampaign =
CALCULATE(
SUM('Sales Line'[sold quantity]),
FILTER(
'Campaign',
'Sales Header'[date] <<<<< I cannot select this from the drop-down, only columns in the campaign table and different measures.

[123abc]

I see that you are trying to filter the 'Campaign' table based on the date in the 'Sales Header' table, which is a common requirement for time-based calculations. However, you're encountering an issue because the 'Sales Header'[date] column is in a separate table and isn't directly related to the 'Campaign' table. To solve this problem, you need to utilize the relationships and DAX functions appropriately.

Since you already have a calendar table and a relationship with the 'Sales Header' table, you should follow these steps:

1. Make sure that the relationship between your calendar table and the 'Sales Header' table is correctly set up. The relationship should be based on the 'date' column.

2. You can use the RELATED function to access columns from related tables. In your DAX formula, use RELATED to access the 'date' column from the 'Sales Header' table. Here's an adjusted DAX formula:

Quantity Sold During Campaign =
CALCULATE(
SUM('Sales Line'[sold quantity]),
FILTER(
'Campaign',
'Campaign'[item no] = EARLIER('Campaign'[item no]) &&
EARLIER('Sales Header'[date]) >= 'Campaign'[campaign start] &&
EARLIER('Sales Header'[date]) <= 'Campaign'[campaign end]
)
)

By using EARLIER('Sales Header'[date]) and RELATED functions, you can reference the 'date' column from the 'Sales Header' table and apply the filter correctly based on your relationships.

This DAX formula should calculate the quantity sold during the campaign period for each item, taking into account the relationships between the tables. Make sure that the relationships are properly set up, and the formula should work as expected.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[MartenB]

Hi, 

I tried that suggestion as well but still no luck. I double checked all relations:

Calendar[date]  1----*  SalesHeader[date]
SalesHeader[OrderNo] 1----* SalesLine[OrderNo]
Campaign[ItemNo]  1----*  SalesLine[ItemNo]

When I write the DAX-code, I get:

Quantity Sold During Campaign =
CALCULATE(
SUM('SalesLine'[sold quantity]),
FILTER(
'Campaign',
'Campaign'[ItemNo] = EARLIER('Campaign'[item no])<<<< Cannot selesct/write this. Get "Parameter is not the correct type"

[123abc]

It seems you're encountering a challenge with the EARLIER function and the context transition in your DAX formula. The EARLIER function is used to refer to a prior row context, but it can be tricky to use in certain situations.

To calculate the quantity sold during a campaign, you can use the following DAX formula:

Quantity Sold During Campaign =
CALCULATE(
SUM('SalesLine'[sold quantity]),
FILTER(
SalesLine,
SalesLine[Item No] = EARLIER(Campaign[Item No]) &&
SalesLine[date] >= EARLIER(Campaign[campaign start]) &&
SalesLine[date] <= EARLIER(Campaign[campaign end])
)
)

This formula uses the CALCULATE function with FILTER to filter the SalesLine table based on the conditions you specified. The EARLIER function is used within the FILTER to refer to the campaign's item number, start date, and end date.

Make sure that you've correctly defined your relationships and the data types of your columns, and that the table names and column names in the formula match your data model. This formula should provide you with the quantity sold during the campaign period for each item.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.