Return Current, Previous and Future Periods

[Anonymous]

Hi All, 

How do i dynamically return current period, previous period and next period (from current)? 

For example, my current period may be 31/7/19 - 15/8/19, so i want to return sales in this current period and then also sales for previous period which is the 14 day period prior to the current period, and then the sales for period AFTER the current period (which is also 14 days ahead). 

But i want to do it dynamically in the sense that, there's not slicer selection, rather based on the date table, it detects if today's date falls within that 2 week range and if so,  based on what the current period is at this point in time, i want to calculate previous and future period sales too as well as sales at that point .
Thanks all

[Anonymous]

Let's say you've somehow filtered your Dates table to a contiguous period of dates. Here's the code to calculate any measure over the past 14 days and the future 14 days.

[Sales for 14 Days Before] =
var __selectedPeriodStart = MIN ( Dates[Date] )
var __beforePeriodStart = __selectedPeriodStart - 14
var __beforePeriodEnd = __selectedPeriodStart - 1
var __beforePeriod =
	DATESBETWEEN (
		Dates[Date],
		__beforePeriodStart,
		__beforePeriodEnd
	)
var __sales =
	CALCULATE(
		[Total Sales],
		__beforePeriod
	)
var __shouldCalculate =
	COUNTROWS( __beforePeriod ) = 14
return
-- We have to check if the __beforePeriodStart
-- is still contained in Dates (has not fallen
-- off the start of Dates). If it has, then
-- we are too close to the start of Dates and
-- the value should not be calculated.
	If ( __shouldCalculate, __sales )
	
[Sales for 14 Days After] =
var __selectedPeriodEnd = MAX ( Dates[Date] )
var __afterPeriodStart = __selectedPeriodEnd + 1
var __afterPeriodEnd = __selectedPeriodEnd + 14
var __afterPeriod =
	DATESBETWEEN (
		Dates[Date],
		__afterPeriodStart,
		__afterPeriodEnd
	)
var __sales =
	CALCULATE(
		[Total Sales],
		__afterPeriod
	)
var __shouldCalculate =
	COUNTROWS( __afterPeriod ) = 14
return
	If ( __shouldCalculate, __sales )

Best

Darek

[Anonymous]

Hi @Anonymous ,

What i need to do is create 2 toggles (horizontal slicers that are responsive) that switches between 2 views.

So i've been given 2 pay periods. One is for the current period and the other is for 14 days after the current period ends. So for example, the current period is from 1/8/19 to 15/8/19 and future period is from  6/8/19 - 30/8/19 .

Now after the 14 days passes, the current period will be 1/9/19 to 15/8/19 and future period will be 16/9/19 - 31/9/19 so what's considered 'current' and 'future' needs to dynamically update based on dates. I basically need the date table to autoupdate every time the database it's connected to gets a new set of dates in it and then based on whether the range is the current period or not, the output is sales for that current period and if i toggle to future period, it sales for the 14 days after the current period ends. 

So what i need to be able to do dynamically is say that if the pay period range is CURRENT PERIOD, then calculate sales and if sales is FUTURE then calculate sales, and continue to do this dynamically. 

Thanks again for all your help.