Forum Discussion

Stelian's avatar
Stelian
Helper I
1 year ago
Solved

DAX measure - filtering dates

 

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 RecIDTyp (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)Startdatum (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)Enddatum (Rückvergütungsvereinbarung) (Bonus & Gegengeschäft)
555666CMS Ersatzteile & Zubehör01/01/202131/12/2025
555666CMS Verbrauchsgüter & Handwerkzeug01/01/202131/12/2025
555666CMS Schmiermittel01/01/202131/12/2025
666777CMS Ersatzteile & Zubehör01/01/202131/12/2025
666777CMS Verbrauchsgüter & Handwerkzeug01/01/202131/12/2025
666777CMS Schmiermittel01/01/202131/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

Need Help
  • Stelian's avatar
    Stelian
    1 year ago
    AC TO LCY =  sum('Actual Sales'[SoldAmount])
    The enter data table is called CMS Customers.
     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

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

    • Stelian's avatar
      Stelian
      Helper I
      1 year ago
      AC TO LCY =  sum('Actual Sales'[SoldAmount])
      The enter data table is called CMS Customers.
       
  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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