Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
So using the filter, the "Current" week should be from 05-02-2018 to 11-02-2018
Solved! Go to Solution.
I have an issue when data is exported to excel sheet, there is no gap between the from date to to date
please find below screenshot
I am using a date table with a field called "Date -1" that I then use as my relative date filter. Here is the Date Table:
DateTable = VAR _minTableDate = MIN ('Basic Calendar'[Date]) VAR _maxTableDate = MAX ( 'Basic Calendar'[Date]) VAR _minDate = IF ( ISBLANK ( _minTableDate ), DATE ( 2018, 1, 1 ), _minTableDate ) VAR _maxDate = IF ( ISBLANK ( _maxTableDate ), DATE ( 2019, 12, 31 ), _maxTableDate ) RETURN ADDCOLUMNS ( CALENDAR ( _minDate, _maxDate ), "Year", YEAR ([Date]), "Date -1", [Date]-1,<--------------------------------------------------------THIS ONE! "Current Year", YEAR(TODAY()), "HalfYear", IF ( MONTH ( [Date] ) <= 6, "1st Half", "2nd Half"), "MonthNumber", MONTH ( [Date] ), "Current Month", MONTH(TODAY()), "YearMonthNumber", FORMAT ( [Date], "YYYY.MM" ), "YearMonthName", FORMAT ( [Date], "YYYY. mmmm" ), "MonthName", FORMAT ( [Date], "mmmm" ), "WeekNum", WEEKNUM ( [Date], 2 ), "CurrentWeek", WEEKNUM (today(), 2 ), "LastDayofCurrentWeek", CALCULATE(MAX('Basic Calendar'[Date]),WEEKNUM('Basic Calendar'[Date], 2 ) =WEEKNUM(TODAY(),2)), "DayNum", DAY( [Date] ), "DayOfWeekNumber", WEEKDAY ( [Date], 2 ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "Current Quarter", "Q" & FORMAT(TODAY(), "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "CurrentYearQuarter", FORMAT ( TODAY(), "YYYY" ) & "/Q" & FORMAT ( TODAY(), "Q" ) )
Although my page filter still appears "wrong" (Sunday-Saturday week) it seems to be adjusting my date table the way I want.
As one example, here is a card to show the reporting time frame:
The measure for the card looks like this:
Weekly Dash Title = "Reporting Timeframe | Week " & DateTable[SelectedWeek] & ": " & MIN ( DateTable[Date] ) & " - " & MAX ( DateTable[Date] )
So the filter appears to be filtering the date table to a Monday-Sunday week which is then appropriately "offsetting" all my other measures since they reference and are related to the date table. There is probably a better work around but this works for me for now. Hope this helps!
It's Disappointing that Microsoft still has not fixed this.
Above proposed solution is not ok if you use any other relative period slicer than week.
If the report consumer would, based on the above solution, ask for last month (calendar), your actualy filtered days would also be shifted forward by one day resulting in wrong date range being selected.
We generated our own Relative dates table. We have strange fiscal periods, and people wanted to do relative period timeframes. Our table has the following timeframes:
"TIMEFRAME" "SORTORDER" "Today" "1" "Yesterday" "2" "Last Weekday" "3" "This Week" "4" "Last Week" "5" "Next Week" "5" "This Period" "6" "This Period, Last Year" "7" "Last Period" "8" "This Year" "9" "Last Year" "10" "Week +2" "11"
We have a third field on the table that is the date field and join that to our Dates table. So for "This Week" timeframe we would have seven entries in the table.
@cplesner,
Currently, it is not possible to change the start day of week in the relative date slicer, there is an idea about this issue, please vote it up.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20248087-relative-date-filter-wee...
Regards,
Lydia
Hi Lydia,
this vote is currently 5 years pending, has thousands of upvotes. This message here has about 20k views.
When will Microsoft schedule this thing to solve?
Care to share?
HOW?
I can't think of a way to do this in Power BI without resorting to DAX or Power Query. There is no where to tell it that you want a Monday start week, not a Sunday.
The Power Query (M) finction Date.StartOfWeek() may be of use in figuring out your weeks. It allows you to state what day your week should atart and returns the start date for the week for the supplied Date.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |