Forum Discussion

Sdd15c's avatar
Sdd15c
New Member
5 months ago
Solved

Dynamic 4-Week Lookback from One Date Slicer

Hi! I’m trying to make 4 visual tables on one page show metrics for different weeks with the dates being controlled by on slicer. My fact table is related to my date table through the Reporting Da...
  • Xian-Zuo's avatar
    5 months ago

    It's possible.

    The scenario you describe is essentially a kind of time intelligence, which can be achieved through measurement values ​​and calculation groups.
    like this.

    Measure = MIN('dim_date'[date])&"~"&MAX('dim_date'[date])
    Measure - 7 day = CALCULATE(
        [Measure]
        ,DATEADD('dim_date'[date],-7,DAY)
    )
    Measure - 14 day = CALCULATE(
        [Measure]
        ,DATEADD('dim_date'[date],-14,DAY)
    )
    Measure - 21 day = CALCULATE(
        [Measure]
        ,DATEADD('dim_date'[date],-21,DAY)
    )

    Another way is to write code in a calculation group that can be applied to different measures, which looks like different slicers are used

     

  • cengizhanarslan's avatar
    5 months ago

    Step 1) Capture the selected Monday

    Selected Week =
    SELECTEDVALUE('Date'[Date])

    Make sure:

    • Your slicer is single-select

    • It uses the Date table (not the fact table)

     

    Step 2) Create offset measures

    Metric – Week 0 =
    VAR SelectedMonday = [Selected Week]
    RETURN
    CALCULATE(
        [Your Metric],
        'Date'[Date] = SelectedMonday
    )
    
    Metric – Week -1 =
    VAR SelectedMonday = [Selected Week]
    RETURN
    CALCULATE(
        [Your Metric],
        'Date'[Date] = SelectedMonday - 7
    )
    
    Metric – Week -2 =
    VAR SelectedMonday = [Selected Week]
    RETURN
    CALCULATE(
        [Your Metric],
        'Date'[Date] = SelectedMonday - 14
    )
    
    Metric – Week -3 =
    VAR SelectedMonday = [Selected Week]
    RETURN
    CALCULATE(
        [Your Metric],
        'Date'[Date] = SelectedMonday - 21
    )

     

    Step 3) Use the correct measure in each table

    Table 1 → use Metric – Week 0

    Table 2 → use Metric – Week -1

    Table 3 → use Metric – Week -2

    Table 4 → use Metric – Week -3

    Each visual now automatically shifts relative to the slicer selection.

  • Ray_Minds's avatar
    5 months ago

    Please find the below steps to achieve that 
    Fact Table

    Date Dimension Table 

    Hence, All values matching as per source data 

    We can also make it dynamic within a single measure – Please feel free to let us know if you need any help around that