Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EricWafford
Regular Visitor

Allow Report Viewer to dynamically change Week Start Day

Greetings, 

I have a requirement that my report should allow the user to dynamically change the Week Start Day, which will in turn shift the visuals accordingly, and my current implementation is extremely "smelly" and doesn't seem scalable. 

 

Currently our implementation only supports changing from Sunday or Monday and is very complex as it stand for just those two days. 

 Currently we generate a table of all dates set up as an offset starting on Monday:

WeekStartMonWeekStartMon

 

And one for Sunday:

Screen Shot 2022-01-26 at 11.26.43 AM.png

 

Then we generate a combined table that has all this data (creating the first "Smell" which is that every date is in the combined table twice):

Screen Shot 2022-01-26 at 11.24.52 AM.png

 

As a note: we generate these from a Date Dimension table that we generate that holds a lot of different time intellegance like features:

Screen Shot 2022-01-26 at 11.25.06 AM.png

So we have this available to us, but can't directly figure out how to do this feature. 

The full modeling looks like this: 
Screen Shot 2022-01-26 at 11.26.01 AM.png

 

We then use this setup to apply a filter that the user can select: 

Screen Shot 2022-01-26 at 11.27.46 AM.png


Combined with some really ugly DAX:

Screen Shot 2022-01-26 at 11.44.26 AM.png

 

We also have to make a M-M relationship with a cross-join between the Date Start Table to any table that needs to "shift" which also feels smelly as well. 


All this to say - this is only supporting two days, and scaling this to include Tues-Friday seems like it would be a nightmare. 

----

Is there a way to use a traditional Date Dimension setup and good RI, but allow a user to dynamically change the week start date of visuals? 

I have direct access to the data and tables, and can make any adjustments to the Date Dimension table or any other aspect to accomplish this, but have been unable to find any direction on how to best pull off a feature like this. Any guidance would be greatly appreciated. 

12 REPLIES 12
JeroenR
Helper V
Helper V

@EricWafford which solution have you eventually come up with or used?

I have the exact same situation as you have, and also created the same solution as you mentioned in the beginning of the post. But was wondering if you have solved this in a more flexible way.

 

Also, have you thought of using Field Parameters to solve this?

TheoC
Super User
Super User

Hi @EricWafford 

 

I use a dax-based Date table and start it with:

 

Date = 

    
VAR _vYearStart = 2014 // Update
    VAR _vYearEnd = YEAR ( LASTDATE ( 'Table'[Ending Date] ) ) // Update
    VAR _vFinYearMonthStart = 7 // Update
    VAR _vToday = LASTDATE ( Date_Reporting[Reporting Date] ) // Update
    VAR _FirstWeekday = 1 // 0 = Sunday , 1 = Monday , etc update

RETURN ...

 

When I saw your post, I created a DayOfWeek table using Enter Data:

 

TheoC_0-1643754896646.png

 

From here, I created a relationship from the DayOfWeek table to the Date table (one to many from the nDate in DayOfWeek to Date), then modified the Date table VAR _FirstWeekday to below:

 

Date = 

    VAR _vYearStart = 2014 // Update
    VAR _vYearEnd = YEAR ( LASTDATE ( 'Table'[Ending Date] ) ) // Update
    VAR _vFinYearMonthStart = 7 // Update
    VAR _vToday = LASTDATE ( Date_Reporting[Reporting Date] ) // Update
    VAR _FirstWeekday = IF ( ISBLANK ( SELECTEDVALUE ( DayofWeek[nDay] ) ) , 1 , SELECTEDVALUE ( DayofWeek[nDay] ) ) // 0 = Sunday , 1 = Monday , etc

RETURN ...

 

I added a drop down to the report using the DayOfWeek fields and it seems to work well and is "dynamic" within the sense of this.

 

Hope this helps mate!

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi, thanks for responding!
I was looking at your suggestion, but I'm lost on one point. 

You mention :


@TheoC wrote:

From here, I created a relationship from the DayOfWeek table to the Date table (one to many from the nDate in DayOfWeek to Date), then modified the Date table VAR _FirstWeekday to below:

But you don't have an 'nDate' in the DayOfWeek. I assume you meant nDay, but when I attempt to recreate this it doesn't work. 

I built a basic DAX based date table like so: 

Date = 
VAR _MinYear = YEAR ( MIN ( pbi_2y_fact_Contribution[Received Date] ) )
VAR _MaxYear = YEAR ( MAX ( pbi_2y_fact_Contribution[Received Date] ) )
VAR _FirstWeekday = IF ( ISBLANK ( SELECTEDVALUE ( DayofWeek[nDay] ) ) , 1 , SELECTEDVALUE ( DayofWeek[nDay] ) ) // 0 = Sunday , 1 = Monday , etc
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= _MinYear, YEAR ( [Date] ) <= _MaxYear )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Week Start", [Date] - WEEKDAY([Date],2) + _FirstWeekday
)


And a DayOfWeek table as described: 

Screen Shot 2022-02-02 at 10.22.49 PM.png

 

I then setup a relationship like you described, I think.. 😄

Screen Shot 2022-02-02 at 10.23.44 PM.png

But this doesn't seem to work, when selecting from the slicer it filters all data out. 

If I attempt to swap the relationship, it tells me I have a circular dependency. 

Screen Shot 2022-02-02 at 10.27.03 PM.png

 

Could you clarify your setup a bit more? 

v-chenwuz-msft
Community Support
Community Support

Hi @EricWafford ,

 

I tried to do it and it seemed to work.
When calculating dynamically, we generally use measure to achieve this, as the column is statically stored and it does not change as the user filters.
I have three tables, a calendar table, a fact table and a week table for slicer. There is a 1 to many relationship between calendar table and fact table.
Create the following measures:

week start day =
VAR _s =
    SWITCH(
        SELECTEDVALUE( 'WEEK'[WeekDay] ),
        "Mon", 1,
        "TUE", 2,
        "WEN", 3,
        "THU", 4,
        "FRI", 5,
        "SAT", 6,
        "SUN", 7
    )
VAR _wn =
    WEEKNUM( ( SELECTEDVALUE( 'Calendar'[Date] ) + 7 - _s + 1 ), 2 ) - 1
VAR _firstweekday =
    DATE( SELECTEDVALUE( 'Calendar'[Date].[Year] ), 1, 1 ) + _s - 5
RETURN
    _firstweekday + ( _wn - 1 ) * 7

The ablove measure is used to return the start day of week. The following formula is put into the filter pane and set show items is 1.

Measure = IF(SELECTEDVALUE('Calendar'[Date])=[week start day],1)

 Result:

vchenwuzmsft_0-1643709272499.png


Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the guidance, I've found one problem with your example, it seems to only work on when the Year is 2021. I modified your example to span two years and now when the Year is 2022, everything is off by a day. 

Screen Shot 2022-02-02 at 1.00.26 PM.png

We provide data over 4 years rolling, so I will need this to work over multiple years. I am looking at your formula but thought I would update you on the progress and see if you had any thoughts. 

Hi @EricWafford ,

 

This is because the weeknum for the first half of the last week of the previous year returned 53, while the second half returned 1. I updated the measure to avoid this.

 

 

week start day =
VAR _firstday =
    CALCULATE( MIN( 'Calendar'[Date] ), ALL() ) - 7
VAR _s =
    SWITCH(
        SELECTEDVALUE( 'WEEK'[WeekDay] ),
        "Mon", 0,
        "TUE", 1,
        "WEN", 2,
        "THU", 3,
        "FRI", 4,
        "SAT", 5,
        "SUN", 6
    )
VAR _firstweekstartday =
    _firstday - WEEKDAY( _firstday, 3 ) + _s
RETURN
    _firstweekstartday
        + ROUNDDOWN(
            VALUE( SELECTEDVALUE( 'Calendar'[Date] ) - _firstweekstartday ) / 7,
            0
        ) * 7

 

vchenwuzmsft_0-1643862618117.png

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the reply! 
The correction provided seems to resolve the issue, but I have noticed now that the Chart used to demonstrate is only showing the Account Value for the first day of a given week, and not a sum of each week. The desired result would be a weekly sum based on this dynamic week start. 

Screen Shot 2022-02-02 at 10.43.00 PM.png

 

I believe this is from the filter Based on your Measure only returning back the "Date" that matches the Start Date, and not all Dates with that Start Date. 

Is there a way to alter this?

Hi @EricWafford ,

 

Make the relationship between calendar and fact inactive or deleted.

sum_account =
CALCULATE(
    SUM( 'Fact Table'[Account] ),
    FILTER(
        ALL( 'Fact Table' ),
        'Fact Table'[Date] >= [week start day]
            && 'Fact Table'[Date] <= [week end day]
    )
)

 

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much for the example! I will look at it this evening. I was toying with a similar idea already, this helps me connect the dots. 

amitchandak
Super User
Super User

@EricWafford , As you need it for axis and a column can not use slicer value, you need need to static columns for each week start date and use those

 

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

I've looked at your suggestion and I do not see how this could solve my need. I am not finding a way to dynamically change the selected Column used to filter my fact table, which seems to be what this would require. This table is great if you write reports where you can pick week start date used, but I need the ability for the report viewer to dynamically select and change it for the entire report. Am I missing something in your suggestion? 

I've recreated a simple example based on your input and my fact table and I don't see how this would allow for that.

 

Screen Shot 2022-01-27 at 9.27.03 AM.png

 

as it stands I am only able to pick one column to use to base the start date off of, unless I am missing something. 

Thank you for responding! 


I'm giving this a shot now, just want to make sure I am clear, I would use the table you are suggesting here in replacement of our current Week Start Date table, and can then create a proper 1<->M relationship of Date Table to Contribution Table, and then allow the user to select which Column of Week Start date(XXX) as the filter?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.