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

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

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors