Filter a Fact Table to Exclude Item Exceptions By Date Range

[RGregory]

I have a list of transactions by Date and Item (multiple dates and items) and now I need to exclude certain items based on the date ranges in this second table.
(items can appear multiple times - item A is offline between 1/1/2023 and 1/4/2023, but also item A is offline 2/15/2023-2/16/2023)

If the transaction date, for that item appears in one of these entries, it needs to be flagged 1, otherwise 0.

...I did set up an inactive many to many relationship on item ID, so I could call upon item IDs to match them, but filtering between the date fields is tricky.

... also, i do have a date table in this model

[RGregory]

Very helpful! Thank you.

I was trying to create this as a calculated column, and doing it the way prescribed above does not work on column.

Instead, I used the steps above to create the Flag as a measure, then used some DAX on column to tie them together.

Thanks!

[Sahir_Maharaj · Accepted solution]

Hello @RGregory,

1. Create a separate table for the exclusion dates with columns for Item, Start Date, and End Date.

2. Create a measure that calculates whether a transaction falls within any of the exclusion date ranges for the item.

Transaction Flag = 
VAR ItemID = MAX('Transactions'[Item ID])
VAR TransactionDate = MAX('Transactions'[Transaction Date])
VAR Exclusions = FILTER(ExclusionDates, ExclusionDates[Item] = ItemID && TransactionDate >= ExclusionDates[Start Date] && TransactionDate <= ExclusionDates[End Date])
RETURN IF(COUNTROWS(Exclusions) > 0, 1, 0)

3. Add the Transaction Flag measure to your visualizations to see which transactions are flagged based on the exclusion dates.

4. You can also use the measure to filter your data by creating a filter that includes only transactions with a flag of 0 (i.e., transactions that are not excluded).

Let me know if you might require further assistance.