Forum Discussion
How to count items at different intervalls
Community ChampionTo 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:
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.
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.
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]
)
)
This measure uses the CALCULATE function to filter the sales data for each item based on the campaign period specified in the 'Campaign' table.
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.
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:
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.
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.
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.