Hello all,
TLDR---
Situation: Tables with *:1 relationship to a calendar that contains week-ending dates.
Goal: Add a visual toggle or list that allows the user to choose between Sunday and Wednesday week-ending days.
Nice to have: no Many to Many, no doubling the calendar table or increasing the size beyond 1-2 columns.
In a nutshell, I have created a powerbi report for us to track a project. The customer wants weekly snapshots of the report sent to them. The issue is that our weekending date is a Sunday and theirs is a Wednesday. Ideally, I would like to have a toggle or list slicer to select your week-ending day of the week so we can switch between Sunday and Wednesday instantly and it affects every visual of the report.
My setup is very straightforward: several queries, many dimensions, and one calendar table with dates and calculated WE dates.
All tables with dates have been replaced with the calendar index.
A few ideas I had that haven't worked:
1. Add a column to the calendar table after the query that calculates the WE date referencing a parameter (does not work since columns are calculated one time)
2. Attempted to create a measure but this was not possible with my skill set.
What could work but is clunky and not as agile/light:
1. Append the calendar with itself after updating the weekending date formula then add a column that is Sunday or Wednesday and use a filter to choose which week-ending date. The issue is this would create a many-to-many relationship which I prefer to avoid at all costs.
If anyone knows a fancy, agile way to make this work, please let me know!
