Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 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

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

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.

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.