You could create a calculated field to determine the Monday that started the current week.
Then use this to create a date filter on your overall data, which should limit the axis to start then.
If you always want 3 months, you'd have to adjust the date filter formula to limit the end of the axis as well. Something like AND Start Date <= dateadd('month',3,WeekStart).
Workbook is attached. Hope this helps!
Sample.twbx 28.1 KB
Thank you very much for the response.
I noticed that the weeks start from Saturday. Should there be any chances made on the calc?
Now, will this pickup Monday as first day of the week irrespective of the day I refresh?
As it's written, it will always pick up the Monday of the CURRENT week, since the "WeekStart" formula is using the TODAY function.
DateTrunc('week') takes the current day and brings it to Sunday, and then I'm adding 1 day to get to Monday. It will always resolve that way since it's based on the current date, not when you refresh the data.
I'm sorry, I misunderstood the requirement. The axis has to stop each Monday, i.e, it should show data from 4/30 to Monday of this week. It should not start from this week's monday.
Logically, that is basically the same. Just flip the logic on your date filter.
If you're updating the start of your axis 4 times/year, you could create a parameter for the date you want the axis to start on (4/30), and then write the filter formula to only show dates in between.
[Start Date]<=[WeekStart] // this is the Monday of the current week
[Start Date]>= [A parameter for whatever date you want the axis to start]
Filter the results for where this is TRUE.
Then when it's time to update the dashboard, you can simply change the parameter value (or allow the users to do that on the dashboard).
I've attached a workbook showing how that would work.
Sample (1).twbx 29.2 KB