cancel
Showing results 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

## Using relative date slicer to get last 3 full weeks

Hi,

Hope anyone found a solution to this.

We are trying to set a dynamic period for the last 3 full weeks. In the example to the left this would be a period from 3-9-2018 to 23-9-2018. This to compare the current (running) week to the last 2 weeks.  Our weeks start on monday but this could also be applied to a week starting on sunday.

Using the relative date slicer we can come up to two period's based on calendar weeks or just weeks.

The result for a relative date slicer on Weeks returns:

From today to 21 days back. week 35 is incomplete

The result for a relative date slicer on Calendar Weeks returns:

The last 3 weeks but without the current week. Week 38 is missing

A solution would be to create a new column with the last 3 weeks marked but if you open this solution another request will be the last 2 or 4 weeks, generating multiple columns.

Would be ideal to get a relative date slicer with a full including current indicator to get the current week/month/year and also X week/month/years back.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous,

Based on my test, Using relative date slicer cannot achieve your goal. However we can create a measure to work asourd.

`Measure = IF(MAX('Table'[week])=WEEKNUM(TODAY()) || MAX('Table'[week])=WEEKNUM(TODAY()) -1||MAX('Table'[week])=WEEKNUM(TODAY()) -2,1,0)`

Then create a visual and filter the visual by the measure.

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
5 REPLIES 5
Anonymous
Not applicable
Community Support

Hi @Anonymous,

Based on my test, Using relative date slicer cannot achieve your goal. However we can create a measure to work asourd.

`Measure = IF(MAX('Table'[week])=WEEKNUM(TODAY()) || MAX('Table'[week])=WEEKNUM(TODAY()) -1||MAX('Table'[week])=WEEKNUM(TODAY()) -2,1,0)`

Then create a visual and filter the visual by the measure.

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thanks. It's a bummer this is not a scenario for the relative data slicer. I will put it on ideas.

Your solution works and I prefer this over new columns and tables because it will not cost any memory and users can create their own if necessary. Because we run on live connection with Azure analysis services tables and columns are a no go so new tables and columns must be added to the central datamodel.

I simplified your code with a variable and also created a "easy customizable measures for this period"

```Last4Weeks =
VAR V_Week =
WEEKNUM ( TODAY (); 2 )
RETURN
IF ( MAX ( 'Calendar'[Week] ) IN { V_Week; V_Week - 1; V_Week - 2 }; 1; 0 )```

Or

```LastXWeeks =
VAR V_Week =
WEEKNUM ( TODAY (); 2 )
VAR V_WeeksBack = 3
RETURN
IF ( MAX ( 'Calendar'[Week] ) >= V_Week-V_WeeksBack && MAX ( 'Calendar'[Week] ) <= V_Week; 1; 0 )```

Would result in:

Solution Sage

The logic below will create a table with the dates you need with weeks starting on monday and returns the current week plus the 2 weeks before that

Now because I guess you are using a matrix with slicers, the same logic can be applied. Basically you have to retrieve the first monday prior to the reference day and then go back 2 weeks

TablesDates =
VAR ReferenceDay =
DATE ( 2018923 ) -- reference day, could be TODAY()
VAR BeginningOfWeek =
ReferenceDay - WEEKDAY ( ReferenceDay2 1 -- beginning of week (Monday) of reference day
VAR BeginningOfLast3Weeks = BeginningOfWeek - ( 7 * 2 )
RETURN
DATESBETWEEN ( Dates[Date], BeginningOfLast3WeeksReferenceDay ),
"Weekday Name"FORMAT ( [Date], "DDDD" ),
"WeekNumber"WEEKNUM ( [Date], 2 )
)

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @LivioLanzo,

Thanks for your response. This is a feasibly solution if the period would be fixed. But i do not want to keep creating columns or tables for certain periods. For sure that this will lead to another request to create a last 4 weeks and 2 weeks table.

Kind regards,

Jordi

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.