Selecting dynamic colums based on slicer selection

[Bobstar86]

Hi

I’m looking for helping to write a DAX measure that selects dynamic columns based on slicer selection.

I have a large dataset on power query that has hundreds of columns. I’m creating a dashboard where one of the slicers will be ‘dates’ allowing user to enter a ‘from’ (start date) and ‘to’ (end date).

Once user enters the dates, the DAX measure should do the following:

1. Filter the main data set and produce a subset data table based on the user’s selection.  
2. Add a new column that calculates percentage return between these two dates. The percentage return will be ‘Return = Divide ((end date, start date – 1*100)’.  

For visualisation, this is how the main data looks like:

Portfolio	31/07/2020	01/08/2020	02/08/2020	03/08/2020	04/08/2020	05/08/2020
P1	100.0	100.5	101.0	101.5	102.0	102.5
P2	100.0	101.0	102.0	103.0	104.0	105.0
P3	100.0	105.0	110.0	115.0	120.0	125.0

If a user selects start date as 02/08/2020 and end date as 04/08/2020, the expected results is as tabled below. The first column (‘Portfolio’) will be static whilst the columns with header dates will be dynamic based on user selections.

Portfolio	02/08/2020	04/08/2020	Return
P1	101.0	102.0	1.0
P2	102.0	104.0	2.0
P3	110.0	120.0	9.0

I would really appreciate your help in writing a measure that can do the above.

Thanks

B

[Anonymous]

Hi @Bobstar86 ,

I suggest you unpivot your table by select all other columns except [Portfolio] column.

New table should look like as below.

Measure:

Measure = 
VAR _StartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR _EndDate = SELECTEDVALUE('End Date'[End Date])
VAR _Value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _StartDate ||'Table'[Date] = _EndDate))
VAR _StartValue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _StartDate))
VAR _EndValue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _EndDate))
RETURN
IF(ISFILTERED('Start Date'[Start Date])||ISFILTERED('End Date'[End Date]),IF(HASONEVALUE('Table'[Date]),_Value,_EndValue - _StartValue),IF(HASONEVALUE('Table'[Date]),SUM('Table'[Value])))

Create a matrix visual and rename column subtotal as Return. Create two unrelated date tables to create start date slicer and end date slicer.

Result is as below.

By Default:

Startdate =2020/08/02, Enddate =2020/08/04

Best Regards,
Rico Zhou

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

[Bobstar86]

Hi @Anonymous 

This is very helpful and is exactly what I was after. Thanks very much.

If you don't mind, I have a few follow up questions that I would appreciate your help with:

1. Do I therefore need to set the data source as an unpivoted table?
2. Is there a way of preventing user from selecting multiple dates?
3. Is there a way to set the date slicers such that a user cannot input a ‘To’ date earlier than 'From' date?
4. My dataset has hundrends of date columns (in pivoted format). Setting the date slicer as drop down means there will be alot of scrolling up and down to select a date. Setting the slicer as between dates (similar to below) with a calendar is much more efficient. Is there a way of doing this instead of the drop down.

Once again thank you so much

Bobstar86