DAX filtering - quantity of product purchased (payment received) by month

[MSFTTrainUser]

Hello!

I am trying to teach myself DAX by creating various visuals, drilldowns, and DAX calculations using the classic database (from MySQL training) ClassicModels (mysqlsampledatabase.sql).  Perhaps some users in the community have used this before.

My goal: plot the monthly income (payment received) for each product code.

My challenges: I have discovered this is not an easy task using the table relationships.    

1) My keys associating the payments table and the order number are different

2) The tables themselves are structured in a way that is not friendly such as one line for ordernumber, several lines for productCode and quantityOrdered.
3) When trying to gather payment dates for each order number, I have not found a way to generically filter the payment dates.  Filter function (or calculate) need to have conditions.  I cannot filter by
Filter('ClassicModels payments','ClassicModels payments'[paymentDate].[month]) <--- I think this is an incomplete expression and needs a qualifier like ="June".  I don't want a qualifier, I only want the payments binned by month received.

4) Gathering sum of each product sold is very straightforward, but this resides in the orderdDetails table so associating the orderNumber and quantity of each item sold for that order with the payments table and month paid is very difficult for me.

What have I tried:
1) I have tried to append all of the tables.  I was successful in doing this although it resulted in a lot of blanks.  I could filter blanks ( ISBLANK(), NOT ISBLANK() etc. ) but this became problematic because if I selected one order number, then the rows containing the other data like payment date would be blank since it was from an appended table.  

2) I have also tried using the tables (not appended) and creating variables that I could use to break down this problem one step at a time.  Unfortunately I ran into challenges related again creating bins for the payment date as well as table structure for quantityOrdered of productCode, per orderNumber.  

for example:

order 1121   productCode 1111     quantity 3
                    productCode 2222     quantity 6
                    productCode 3333     quantity 9

If I was using Power Query then I could use a filldown to fill in the order number so that all rows contained the order number, then use a simple filter.

3) Use of DAX functions.. just getting used to the syntax ... which I refer to the documentation and other online resources.  I hope I'm not making any stupid oversights. 😊

Summary:

🤣  So, I feel like I should have started getting my hands dirty with DAX using an easier challenge to solve.  I know that I have added a lot of details here so I would appreciate any suggestions on:

* append / don't append
* handling table relationships with different keys
* "generic filtering" as in binning payments by month- only month
* and using data in tables that are structured but not all cells in a rows is populated

[Anonymous · Accepted solution]

Hi @MSFTTrainUser ,

Consider merging the order table and the payment table to match multiple columns to get the amount, and I believe the other problems will be solved.

Merge queries overview - Power Query | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.