Filter By Date Relationship

[BrianNeedsHelp]

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. 

[Anonymous]

Hi @BrianNeedsHelp ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

Hi @BrianNeedsHelp 

The issue you're experiencing is due to the lack of a date relationship between your 'Current Hierarchy 2' table (which contains 'Location Quota') and your 'Calendar 2' table. Because 'Location Quota' is determined by month, but there is no date linking 'Current Hierarchy 2' to 'Calendar 2', the date filters in your report are not affecting the 'Location Quota' totals, causing it to sum over all years.

Solution:

1. Add a Date Column to 'Current Hierarchy 2':

   • Include a Date or Month Column:
     • Add a date column to the 'Current Hierarchy 2' table that represents the month and year for each 'Location Quota' entry.
     • For example, you can use the first day of the month for each quota period.

2. Create a Relationship Between 'Current Hierarchy 2' and 'Calendar 2':

   • Establish a Date Relationship:
     • Create a relationship between the new date column in 'Current Hierarchy 2' and the 'Date' column in 'Calendar 2'.
     • Ensure the relationship is set correctly to allow filter propagation from 'Calendar 2' to 'Current Hierarchy 2'.

3. Adjust Your Measures and Visuals:

   • Use Standard Aggregations:
     • With the date relationship in place, your 'Location Quota' will now respond to date filters applied in your reports.
     • You can use the SUM of 'Location Quota' without additional complex measures.
   • Filter by 'Date Closed':
     • When you filter your report by 'Calendar 2'[Month Year], it will now correctly filter the quotas to the desired month.

Alternative Measure (If Adding a Date Column Is Not Feasible):

If you cannot modify the 'Current Hierarchy 2' table, you can create a measure that filters the 'Location Quota' based on the 'Date Closed' in 'BCP':

SumLocQuotaFiltered =
VAR ClosedDate = SELECTEDVALUE('BCP'[Date Closed])
VAR StartOfMonth = DATE(YEAR(ClosedDate), MONTH(ClosedDate), 1)
VAR EndOfMonth = EOMONTH(ClosedDate, 0)
RETURN
CALCULATE(
    SUM('Current Hierarchy 2'[Location Quota]),
    FILTER(
        'Current Hierarchy 2',
        'Current Hierarchy 2'[Sales Code] = SELECTEDVALUE('BCP'[UniqueID])
    ),
    FILTER(
        'Calendar 2',
        'Calendar 2'[Date] >= StartOfMonth &&
        'Calendar 2'[Date] <= EndOfMonth
    )
)

Notes:

• Ensure Single Values:
  • SELECTEDVALUE works when there's only one value in the current context. Make sure your visuals are set up to provide a single 'Sales Code' and 'Date Closed' per row.
• Performance Consideration:
  • Using complex measures with filters can impact performance. Adding a date column and relationship is more efficient.

Recommendation:

For the best results, adding a date column to 'Current Hierarchy 2' and establishing a proper relationship with 'Calendar 2' is the most effective solution. This aligns your data model properly and allows Power BI's built-in filtering to work as intended, ensuring accurate totals without complex measures.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube