Display Custom Timeframe by Week

[sharc316]

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 like to display the most recent Sat-Fri date range and have it update every Wednesday once new statements come out.

Here is an example

Wednesday 4/12: date range to display Sat 4/1- Fri 4/7

Wednesday 4/19: date range to display Sat 4/8- Fri 4/14

If possible to make data refresh as the trigger to switch the date ranges.

Maybe an easier way to do this would be to display previous 7 days from max date in the data. So when the refresh is loaded it will adjust to show the most recent week.

Any help would be greatly appreciated.

[Anonymous]

In the projects i work on, i have a DIM - Date Table.  I've created a column in these table called "Include in report".  This is a simple True/False value on whether this date should be included.  I then use that column on my report filters.

[v-huizhn-msft]

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]

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 · Accepted solution]

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]

@v-huizhn-msft

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

[v-huizhn-msft]

Hi @sharc316，

Have you resolved your problem?  If you havem, please mark helpful reply as answer, or welcome to share your own solution, more people will benefit from here.

Best Regards,
Angelia

[sharc316]

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

[dkay84_PowerBI]

You can create a column that has the logic you need (ie displays a true/false for each record if the date falls in the window) and add that to the report as a global or page filter.