Shift dates based on slicer

To achieve the described functionality, you'll need to create a calculated column or measure in Power BI that calculates the halfway point for each client based on the selected product or region. Here's a breakdown of the steps:

1. Identify the Selected Product/Region: You need to identify the selected product or region using a slicer. This will be used to filter the data.

2. Calculate Halfway Point for Each Client's Journey: Determine the halfway point between the start date and end date for the selected product or region of each client. This will be the shifting point for each client's journey.

3. Shift the Dataset: Shift the dataset for each client based on the calculated halfway point.

Here's how you can implement this in DAX:

ShiftedStartDate =
VAR SelectedProductOrRegion = SELECTEDVALUE('client-data'[PRODUCT])
VAR ClientID = 'client-data'[ClientNr]
VAR HalfwayPoint =
CALCULATE (
MIN('client-data'[STARTDATE]) +
(MIN('client-data'[ENDDATE]) - MIN('client-data'[STARTDATE])) / 2,
FILTER('client-data', 'client-data'[PRODUCT] = SelectedProductOrRegion),
FILTER('client-data', 'client-data'[ClientNr] = ClientID)
)
RETURN
'client-data'[STARTDATE] + HalfwayPoint - CALCULATE(MIN('client-data'[STARTDATE]), 'client-data'[ClientNr] = ClientID)

This DAX formula assumes that 'client-data' is your client table, and you have columns like [ClientNr], [STARTDATE], [ENDDATE], and [PRODUCT]. Replace these with your actual column names.

• The SELECTEDVALUE function gets the selected product or region from the slicer.
• The CALCULATE function calculates the halfway point for the selected product or region for each client.
• The FILTER function filters the data to calculate the halfway point for each client.
• The RETURN statement returns the shifted start date based on the halfway point.

You can create a calculated column or measure using this DAX formula, depending on your requirement. Adjust the formula according to your data model and relationships.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.