How to count items at different intervalls

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.

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.