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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
123abc
Community Champion

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.

 
 
 

View solution in original post