table relationships
1 Topic
Filter By Date Relationship
In the model if I select 'Quota Results'[Location Quota] and 'Current Hierarchy 2'[Sales Code] and then put a filter in the report for 'Calendar 2'[Month Year]October 2024, it correctly shows the Sales Codes and Location Quota for the month. But I've added another table that has the fields 'BCP'[UniqueID] and 'BCP'[Date Closed] among others. UniqueID is equivilant to Sales Code. I created a table using DAX: Table Location Quota = SELECTCOLUMNS( 'Current Hierarchy 2', "SalesCode", [Sales Code], "LocQuota", [Location Quota]) I then created relationships between 'BCP'[UniqueID] and 'Current Hierarchy'[Sales Code] and relationship 'BCP'[Date Closed] and 'Calendar 2' [Date]. Now when I select "SalesCode" and "LocQuota" I get individual totals for the location. But the LocQuota is summing the quota for several years instead of the month of when location was closed in 'BCP'. I've tried numerous measures but I get the same grand total of all the locations quota in each row. Any insight into how to filter it for the month would be greatly appreciated. BTW Location Quota is determined by Month whereas Date Closed is a date like Friday, October 2, 2024, so I even created a measure StartofMonth=STARTOFMONTH('BCP'[DateClosed]) and then a measure: QuotaDateFilter = CALCULATE([SumLocQuota],FILTER(All('Calendar 2'),'Calendar 2'[Date]=('BCP (2)'[StartofMonth]))) It takes up too much resource or other measures I've tried put the grand total of all the locations in each row. Please help.Solved974Views0likes2Comments