Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Return Current, Previous and Future Periods

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 thi...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.