Ignore earliest date and only use latest date from slicer on visual

[TJHughes]

I have a page which have a page which shows one visual the sales data for week selected with comparison to prev year. Another visual on same page shows the YTD sales, and comparison to prev year. There are 2 slicers, on to select the financial year and the other to select the week number. The week sales visual works fine, but the YTD is the one I am having issues with. The financial year slicer is fine, but the week number gives to it earliest and latest date for the week selected. If I disable the week number slicer for YTD visual then it just shows the whole financial year, I need it to show up to the end of the week selected. So it needs to ignore the earliest date of the week number slicer but not the latest date.

I tried to use another slicer which is filtered by the finacial year and week number slicers and set it to before date and use that slicer along with finacial year on the YTD visual and not the week number slicer. This works, exept the slicer does not auto refresh when the week number slicer is changed. You need to update the new slicer manually for it to work.

Any way I can get the new slicer to auto update to latest date when the week number slicer to changed or a better way to do what I need?

[Anonymous]

Hi @TJHughes ,

May I ask if your problem has been solved? if it has been solved you can mark the above answer as a solution, if it has not been solved, please provide more details with your desired out put and pbix file without privacy information. This will help us to better understand and solve your problem.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

^{Best Regards,}

^{Ada Wang}

[123abc · Accepted solution]

In Power BI, the behavior you are experiencing is because of the relationships between your tables and the way slicers interact with them. You want to create a YTD (Year-to-Date) sales visualization that is dynamically filtered by both the financial year and the week number, but you don't want the week number to affect the end date of the YTD calculation.

Here's a step-by-step approach to solve this issue:

1. Create a Date Table:

Ensure that you have a Date table in your model that contains a unique list of dates and related columns like Year, Month, Week Number, etc.

2. Create Relationships:

Make sure you have relationships set up between your Date table and your sales data table. You might have a relationship based on the Date or Week Number.

3. Create Calculated Columns:

In your Date table, create calculated columns to identify the start and end dates of each week based on the Week Number and Year

Start Date of Week =
VAR CurrentDate = YourDateTable[Date]
RETURN
CurrentDate - WEEKDAY(CurrentDate, 2) + 1

End Date of Week =
[Start Date of Week] + 6

4. Create Measures:

Create measures to calculate YTD sales based on the date ranges you have set.

YTD Sales =
CALCULATE(
SUM('Sales'[Amount]),
DATESYTD('DateTable'[Date])
)

5. Create a Slicer:

Create a slicer for Financial Year and another slicer for Week Number.

6. Adjust the YTD Visualization:

For your YTD visualization, use the Financial Year slicer and create a new slicer using a disconnected table (or a disconnected copy of your Date table). This disconnected slicer should allow you to select a range of dates that correspond to the selected week number without affecting the YTD calculation's end date.

7. Use the Disconnected Slicer in a Measure:

You can use the selected date range from the disconnected slicer in a measure to filter your YTD calculation dynamically.

Filtered YTD Sales =
VAR SelectedStartDate = SELECTEDVALUE('DisconnectedTable'[Start Date of Week])
VAR SelectedEndDate = SELECTEDVALUE('DisconnectedTable'[End Date of Week])
RETURN
CALCULATE(
[YTD Sales],
'DateTable'[Date] >= SelectedStartDate && 'DateTable'[Date] <= SelectedEndDate
)

8. Test and Validate:

Test the solution by changing the week number in the slicer and see if the YTD visualization updates correctly up to the end of the selected week.

This approach uses a disconnected slicer table to allow you to control the end date of your YTD calculation dynamically while keeping the financial year consistent. By using DAX measures that reference the selected date range from the disconnected slicer, you can achieve the desired behavior in your YTD visualization.