Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
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.
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
Thank you I will try this method. Really appreciate the time you took to help.
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
Thank you for your help. I will give this a try.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |