Forum Discussion
Table expansion measure
Anonymous wrote:
At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Your data model does not look correct to try and get that sort of result.
Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is $10 in that account when you list the account balance by person you would see the following:
Husband $10
Wife $10
Total $10
So the huband and wife cannot both spend $10, if the husband spends $10 there will be 0 left in the account.
This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had $5 of sales in Sep 19 and $5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of $10.
Thanks for the reference. The problem is that is not possible to reference from calendar table to the other table with an inactive relation. At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Thanks in advance!
Anonymous wrote:
At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Your data model does not look correct to try and get that sort of result.
Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is $10 in that account when you list the account balance by person you would see the following:
Husband $10
Wife $10
Total $10
So the huband and wife cannot both spend $10, if the husband spends $10 there will be 0 left in the account.
This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had $5 of sales in Sep 19 and $5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of $10.