Forum Discussion

sharc316's avatar
sharc316
Helper I
9 years ago
Solved

Display Custom Timeframe by Week

Hi,   I would like to create a custom weekly timeframe that shifts every week. Here is the situation:   There are statements that come out every Wednesday for the previous week Sat-Fri. I would l...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi sharc316,

    Sorry for misunderstanding your requirement in the original post.

    In order to show clearly, I create the sample table including date from 2017/4/1 to 2017/4/30.

    1. Create another date table only including date column, like Table2 in mine .pbix file.

    2. Create a slicer including Table2[Date], create a measure get the day's weeknum selected .in slicer.

    select Date = WEEKNUM( CALCULATE(MAX(Table2[Date]),ALLSELECTED('Table2')))

    3. Create another measue to get previous week date in Table.

    Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Week]=Table2[select Date]-1))


    Create a table visual, select date and Measure as value level, when you select the 2017/4/19, it returns the previous week's date.



    Please download the file and test, please feel free to ask if you have other problem.

    Best Regards,
    Angelia


v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi sharc316,

1. If you click the "refresh" buttom, it will refresh the lasted date in resource data table.

2. When the data is Wednesday 4/19, you want to display Sat 4/8- Fri 4/14 in report or your resource table? If you want to return in reource table, it's impossible as the 1 posted. If it display in report, create a calculated column.

Lastest Wednesday=CALCULATE(MAX(Table[date]),FILTER(Table, Table[week]="Wednesday"))

Previous weeknum=LOOKUPVALUE(Table[weeknum],Table(date),Table[Lastest Wednesday])-1


3. Create a new table to return the expected data range by clicking "New Table" under Modeling on Home page.

Range=SELECTCOLUMNS(TFILTER(Table,Table[weeknum]=Table[Previous weeknum]),"Date",Table[date])


Best Regards,
Angelia

  • sharc316's avatar
    sharc316
    Helper I
    9 years ago

    Thank you for your help. I will give this a try.

  • sharc316's avatar
    sharc316
    Helper I
    9 years ago

    Hi v-huizhn-msft,

     

    I've attempted your solution but was not able to make it work. The two lines of code in step 2; are these two separate calculated columns?

     

    Also, my dates are listed by day to it would need to include all data within a range of a particular week. I'm new at this, sorry if I'm missing something.

     

    Thank you for your time.

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi sharc316,

      Sorry for misunderstanding your requirement in the original post.

      In order to show clearly, I create the sample table including date from 2017/4/1 to 2017/4/30.

      1. Create another date table only including date column, like Table2 in mine .pbix file.

      2. Create a slicer including Table2[Date], create a measure get the day's weeknum selected .in slicer.

      select Date = WEEKNUM( CALCULATE(MAX(Table2[Date]),ALLSELECTED('Table2')))

      3. Create another measue to get previous week date in Table.

      Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Week]=Table2[select Date]-1))


      Create a table visual, select date and Measure as value level, when you select the 2017/4/19, it returns the previous week's date.



      Please download the file and test, please feel free to ask if you have other problem.

      Best Regards,
      Angelia


      • sharc316's avatar
        sharc316
        Helper I
        9 years ago

        v-huizhn-msft

        Thank you I will try this method. Really appreciate the time you took to help.