Measure to remove table filter to add sales orders for next 14 days in the future from today

[jpt1228]

Hello, I have a matrix visual that is filtered by a relative date filter. I want to create a measure that will ignore that filter and bring in sales orders that are in the next 14 days fom today.

Here is what I have so far. But it is bringing in all the sales orders in the future.

Sales Orders After Today No Filter = CALCULATE(FactSalesOrderLines[Cases Ordered],
DATEADD(DimDate[Date],+14,DAY),
FILTER (
ALL(DimDate[Date]),
DimDate[Date] > TODAY()
)
)

[Phil_Seamark · Accepted solution]

Hi @jpt1228

Here is one way to do it.  Just make sure you use the Date field from DimDate on your Axis and may need to remove the relationship between the two tables.

Sales Orders After Today No Filter = 
	CALCULATE(
		SUM(FactSalesOrderLines[Cases Ordered]),
		FILTER(
			'DimDate',
				[Date]>=TODAY() && 
				[Date]<TODAY()+14
				)
		)

[Phil_Seamark · Accepted solution]

Hi @jpt1228

Here is one way to do it.  Just make sure you use the Date field from DimDate on your Axis and may need to remove the relationship between the two tables.

Sales Orders After Today No Filter = 
	CALCULATE(
		SUM(FactSalesOrderLines[Cases Ordered]),
		FILTER(
			'DimDate',
				[Date]>=TODAY() && 
				[Date]<TODAY()+14
				)
		)

[jpt1228 · Accepted solution]

Hello @Phil_Seamark - I was able to get this to work in a new matrix visual but when I add the measure into the other matrix visual that has relative date filtering it doesn't work.

Here is the formula that I got to work. Since I had a page filter using the relative date filter set to "Today" This formula works to bring in the sales in the next 14 days from the page filter date. This also works to bring in sales previous to the date filter as well.

SO Cases in next 14 days =
CALCULATE(
FactSalesOrderLines[Cases Ordered],
DATESINPERIOD(DimDate[Date],
LASTDATE(DimDate[Date]),
+14, DAY
))

Thanks