Sum of Transactions Containing Filtered Items outside Filtered Date Range

[dwelsh]

I have a request to display different type of sale information for items grouped by a filter called Flyer. The data needs to be grouped by a filtered date and the 90 days prior to the filtered date. Here is my data sources and relationships:

I have been able to create most of the measures I need but there is one I am struggling with. For the 90 day prior period, I need to get the transactions total when any item on the Flyer filter group is on a transaction. In other words, for all transactionIDs with a flyer item on it in the prior 90 day period, I want to sum all items on these transactionIDs to find all item sales.

Below are some of the measures I have been able to get to work and confirm they return correct values:

Sale total for Items in filtered date range and Flyer Group:

Sales Period = sum(Sales[Sales])

Sale total for Items 90 day prior to filtered date range and filtered Flyer group:

Sales Prior = CALCULATE(sum(Sales[Sales]),DATESBETWEEN(dimDate[Date],[Period Prior 90 Start Date],[Period Prior 90 End Date]))

Transactions Total for Items:

Sales With Flyer Items = CALCULATE([Sales Period], DISTINCT(Sales[TransactionID]),all(FlyerItems[Flyer]))

Here is what I came up with but it doesn't return any results:

Sales With Flyer Items Prior = CALCULATE([Sales Prior], DISTINCT(Sales[TransactionID]),all(FlyerItems[Flyer]))

I believe it is failing because DISTINCT(Sales[TransactionID]) is not returning any results in the Sales Prior period but I don't know how to fix it. 

Any suggestions?

[amitchandak]

@dwelsh , What are trying to do here

Sales With Flyer Items = CALCULATE([Sales Period], DISTINCT(Sales[TransactionID]),all(FlyerItems[Flyer]))

This can be like

Sales With Flyer Items = CALCULATE(sumx(values(Sales[TransactionID]), [Sales Period]), ,all(FlyerItems[Flyer]))

Same way the last one

Last two what you are trying to do

[dwelsh]

Thanks @amitchandak. I apologize I wasn't clear. The 'Sales With Flyer Items' measure is working correctly. I am trying to do the same thing with the measure 'Sales With Flyer Items Prior' but it is coming back blank when using:

Sales With Flyer Items Prior = CALCULATE([Sales Prior], DISTINCT(Sales[TransactionID]),all(FlyerItems[Flyer]))

I think this because in the above DISTINCT(Sales[TransactionID]) is only returning TransactionIDs from the filtered period and not from the Prior 90 Day date range.  How can I get the sum of all transactions from the past 90 days where at least one flyer item is on the transaction?