Forum Discussion

PineTree's avatar
PineTree
Regular Visitor
2 years ago
Solved

Toggle Between Week Ending Dates

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! 

 

2 Replies