Forum Discussion
How to count items at different intervalls
Community ChampionI 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.