Shift dates based on slicer

Shliim_1 — Mon, 19 Feb 2024 08:53:58 GMT

Hi,

I need a pretty difficult DAX command which I can't figure out.

End-goal: I need to compare customer journies of our clients . Therefore I need/want to overlap the journies over each other.

How I imagine it:

the user selects a product or region using a slicer which applies it on a table called "client-data"
1. the user shouldn't be able to filter on a client but a product or region. on which the user likes to zoom in.
2. the "client-data" table is uncoupeled from the "Master" table because one client has multiple lines with different products or regions which all need to be taken into account.
The halfwaypoint (End_date - Start_date)/2 of the selected product of each client needs to used to shift the whole dataset of the client.
if the selected product is repeated in use by a client, choose the first one.

visually ->

clienttable:

Region	PRODUCT	ClientNr
1	PR_1	CL_1
1	PR_2	CL_1
2	PR_3	CL_2
2	PR_2	CL_2
3	PR_1	CL_3
3	PR_3	CL_3

MasterTable

CLIENT	STARTDATE	ENDDATE	PRODUCT	...>
CL_1	DATE 1	DATE2	PRODUCT1	..>
CL_2	...	....	...	...>
...	...	...	...	..

Re: Shift dates based on slicer

123abc — Mon, 19 Feb 2024 13:28:46 GMT

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:

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.
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.
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.

topic Re: Shift dates based on slicer in DAX Commands and Tips

Shift dates based on slicer

Re: Shift dates based on slicer