Set analysis sum where date from other table is equal to date in the dimension
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.
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).