Set analysis sum where date from other table is equal to date in the dimension

[roqv2018]

I have two separate tables, one of them has customer data and the other has accounting data.

Both tables share a common key so that I can link accounting data to customers, but I want to keep both tables separate because I want to summarise data from accounting for each customer record and want to avoid row duplication. 

Lets say these are my tables: 

I want only data from the customer in the dimensions but want to sum accounting data in the expressions so that I can show postings on account "123456" in one expression and postings on account "56789" on another expression. I want to show postings depending on the key and also to sum the amount only when this is smaller than or equal to the reporting date.  In this way I want to show a rolling balance depending on the reporting date being looked at.

I tried this set expression but it does not work:

=Sum({1<posting_date={"<=$(=reporting_date)"}, account={'123456'}>}amount)

I also want to show all customer data even if there are no postings on the account.

Appreciate any tips. 

Sample qvw attached.

[Lisa_P]

I think that due to the combinations of values  related based on they key there are ambiguous results so this table and expression will not work. When you select one reporting date, you get a result.

[Arthur_Fong]

Do you mean like this?

In script,  do not link the key with the two tables.

Rename the key field so that the tables doesn't link.

Then in your expression, try this:

=Sum({<posting_date={"<=$(=max(reporting_date))"}, account={'123456'}>}if(key=keyAccount, amount))

Refer attached qvw as reference.

Thanks and regards,

Arthur Fong

[roqv2018]

Not quite like that. I want to display the reporting date and only postings where the posting date is less than or equal to the reporting date. We would have different sums for September and October. The way you did it, you have the same balances in September and October because you used max(reporting_date).

[Arthur_Fong]

Can you please attach an expected result?

[roqv2018]

This is a picture of the expected result