cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cplesner
Helper II
Helper II

How to set build-in Relative Date filter to use monday as first day of the week

So using the filter, the "Current" week should be from 05-02-2018 to 11-02-2018

 

Capture.PNG

1 ACCEPTED SOLUTION

9 REPLIES 9
kalyanmaddali2
New Member

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 

from date to to date .jpg

 

Anonymous
Not applicable

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.

Wrong looking filterWrong looking filter

As one example, here is a card to show the reporting time frame:Title CardTitle Card

 

 

 

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!

dramus
Responsive Resident
Responsive Resident

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.

v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dspbi
Frequent Visitor

Care to share? 

HOW?

dramus
Responsive Resident
Responsive Resident

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.

dramus
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors