Using relative date slicer to get last 3 full weeks

[Anonymous]

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.

[Anonymous]

As an idea https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35438311-relative-data-slicer-las...

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Hi @v-frfei-msft,

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:

[LivioLanzo]

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 ( 2018, 9, 23 ) -- reference day, could be TODAY()
VAR BeginningOfWeek =
    ReferenceDay - WEEKDAY ( ReferenceDay, 2 ) + 1 -- beginning of week (Monday) of reference day 
VAR BeginningOfLast3Weeks = BeginningOfWeek - ( 7 * 2 )
RETURN
    ADDCOLUMNS (
        DATESBETWEEN ( Dates[Date], BeginningOfLast3Weeks, ReferenceDay ),
        "Weekday Name", FORMAT ( [Date], "DDDD" ),
        "WeekNumber", WEEKNUM ( [Date], 2 )
    )

[Anonymous]

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