Forum Discussion

pola's avatar
pola
New Member
2 years ago
Solved

How to build dynamic X-axis showing four weeks back depends on week selected in slicer?

I want to build the line chart showing 4 data point after 4 consecutive weeks. My users select a given week in the slicer and the line chart displays 4 weeks back in cluding selected week. I specificly struggle to build the measure which accepts dynamic max and mix constraints... Any idea? 

4 Replies

  • RPYee's avatar
    RPYee
    Frequent Visitor
    2 years ago

    I did something similar, wanting to display the previous 13 weeks data. I may not have done it the easiest way possible, but it does work for me. Hopefully it will help you!

     

    First of all, I created a new column to calculate the report date which is based on the date of the previous Saturday. Our weeks are viewed Sunday thru Saturday.

     

    WKLY_RPT_ENDDATE = TODAY()-WEEKDAY(TODAY())

     

    I then assigned a number for each of the previous weeks, calling this "WEEKSAGO"

    WEEKSAGO = FLOOR((-(DATEDIFF(SOURCE_TABLE[WKLY_RPT_ENDDATE],SOURCE_TABLE[ARRIVALDATE].[Date],DAY))/7)+1,1)

    When I build my visualizations, I place a filter for WEEKSAGO>0 and WEEKSAGO<14


    • pola's avatar
      pola
      New Member
      2 years ago

      Thanks for your input! Unfortunately, that is not the same scenario. In my case we want to show data for 4 weeks back depends what week the user selects in slicer.  so if user selects week 14 of the year, I want to see weeks: 11,12,13 and 14 on my X axis, when user selects week 20 -- I want X axis to adjust to show 17,18,19, 20.... for some reason filtering does not interact well whit selected value..