Filter By Date Relationship

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