Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. Th...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

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

    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: