DAX measure - filtering dates

[Stelian]

I've created a Power BI report linked to a dataset. The table I’m interested in is Actual Sales, which contains Customer RecID, Invoice Date, and a measure [AC TO LCY].

Additionally, I have a manually created table in Power Query using "Enter Data" with the following structure:

Customer RecID	Typ (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)	Startdatum (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)	Enddatum (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)
555666	CMS Ersatzteile & Zubehör	01/01/2021	31/12/2025
555666	CMS Verbrauchsgüter & Handwerkzeug	01/01/2021	31/12/2025
555666	CMS Schmiermittel	01/01/2021	31/12/2025
666777	CMS Ersatzteile & Zubehör	01/01/2021	31/12/2025
666777	CMS Verbrauchsgüter & Handwerkzeug	01/01/2021	31/12/2025
666777	CMS Schmiermittel	01/01/2021	31/12/2025

I created a many-to-many relationship between the dataset and this table based on Customer RecID.

I need to calculate the sales value for each customer for all Invoice Dates that fall between Start Date and End Date for specific product groups.

Here is my measure:

test = VAR Result = CALCULATE( [AC TO LCY], ProductDim[Product CRM SubGroup Code] IN {"WA-01", "WA-02", "WA-03"}, 'Actual Sales'[InvoiceDate] >= SELECTEDVALUE('CMS customers'[Startdatum]), 'Actual Sales'[InvoiceDate] <= SELECTEDVALUE('CMS customers'[Enddatum]) ) RETURN Result

The filtering on Product CRM SubGroup Code works correctly, but the filtering on Invoice Date does not seem to work as expected.

Can anyone help me understand what might be wrong, please?

Thank you,

Steli

[speedramps]

Your question is too vague.

Thanks for the example data of the Enter Data table.

PHowvere, you have neglected to provide  the Actual Sale table.

Futhermnore it does not contain the Customer RecID which you say has relationship.

Please dont copy and paste your buggy DAX and expect us to know what you require.

Please do provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Then a clear step-by-step decscription and use consistent table and field names in the input, output and description.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Thank you

[Greg_Deckler]

@Stelian What is the formula for your AC TO LCY measure? Also, what is that enter data table called?

[Stelian · Accepted solution]

AC TO LCY =  sum('Actual Sales'[SoldAmount])
The enter data table is called CMS Customers.