Confused Data Help - New to Power BI

[eddieatchisonam]

Greetings All! 

I am a very new user to Power Bi and having a bit of trouble. I just can't wrap my head aroud what I need. I'm hoping someone can help me understand the logic behind getting to the data that I need. Please let me know if I didn't explain clearly or give enough direction. This is really hard to even exlain. Thank you so much in advance for the support and help. 

This is a collection report.

I have a list of Accounts Assigned
I have a list of Transactions. 
(these are relationship by account number.)

What I ultimately need - I need to know if Mickey Mouse was in the Accounts assigned list say December 2020, How much did Mickey Mouse actually pay on any other accounts during the same time period. 

So - in an excel world - I think i would index/match the Name from accounts, and match it to all the transactions. Then I would have name in both queries. then some really long IF statement to basically say - IF mickey exists in Assigned Date for selected time frame - sum transaction amount from same date. Or something along those lines. 

As I said this is soooooo hard to explain. Does anyone remotely understand what I'm trying to achieve? What other info do you need from me to try and help. Thanks again. 

(accounts[ Assigned Amount],
(accounts[ Name1],
(accounts[Account Number],
(accounts[Assign Date],
(transactions[Amount],
(transactions[Transaction Date],
(transactions[Account Number],

So:when i click the slicer to select Dec and 2020,
I need a probably list to show:

(transaction[Account Number] first lookup in (accounts[Name1]

IF (accounts[Name1] exists in (transaction[Account Number], then sum (transactions[Amount] for any (accounts[Account Number] during (transactions[Transaction Date]

I'm more confused than when I started writing this. Thanks for the help or even the time to try and understand my confused world. 

Have a great day. 

[eddieatchisonam]

What a month this has been! THank you so much for the time and help. I've loaded that in, and it doesn't seem to be giving the results desired. I'm going to work on a bit more to see if I can give a better explanation. 

[Ashish_Mathur]

Hi,

It will be great if you can share your working MS Excel file with your formulas in there so that your logic can be understaood and translated in the DAX formula language.

[v-yingjl · Accepted solution]

Hi @eddieatchisonam ,

Based on your description, I have created a sample table and you can create a measure like this:

AccountsTransaction

Measure = 
CALCULATE (
    SUM ( 'Transaction'[Amount] ),
    FILTER (
        'Accounts',
        'Accounts'[Account Number]
            IN DISTINCT ( 'Transaction'[Account Number] )
                && 'Accounts'[Assign Date].[Year]
                    = SELECTEDVALUE ( 'Transaction'[Transaction Date].[Year] )
                && 'Accounts'[Assign Date].[Month]
                    = SELECTEDVALUE ( 'Transaction'[Transaction Date].[Month] )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.