PowerBI - selecting dynamic column based on slicer selection

[Bobstar]

Hi

I’m looking for helping writing a measure that selects a dynamic column based on slicer selection.

I have a large data set 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). This would then filter the main data set and produce a subset data based on the user’s selection. A new column will then calculate percentage return ((end date/start date – 1)*100) over that period.

For visualization, this is how the data should look like

Main data

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 user selects start date as 02/08/2020 and end date as 04/08/2020, a subset table will be generated as follows. The first column ("Portfolio") will be static but the two columns with dates will be dynamic based on user selection.

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

 

[Matt Allington]

First, unpivot the date columns so you have portfolio, date, value. The rest is covered in my video here

Compare Any Two of Anything in Power BI

I have been increasingly recording videos as my method of sharing tips and techniques for getting more out of Power BI. Some concepts are best documented (traditional blogging) and some are just easier for everyone if it is a video. That is the case again for today. Compare Any Two [...]Read More »

exceleratorbi.com.au

 

[Bobstar]

First, unpivot the date columns so you have portfolio, date, value. The rest is covered in my video here

Compare Any Two of Anything in Power BI

I have been increasingly recording videos as my method of sharing tips and techniques for getting more out of Power BI. Some concepts are best documented (traditional blogging) and some are just easier for everyone if it is a video. That is the case again for today. Compare Any Two [...]Read More »

exceleratorbi.com.au

Thanks Matt. The video is very helpful. I shall give it a go using instructions from your video.

 

[Bobstar]

First, unpivot the date columns so you have portfolio, date, value. The rest is covered in my video here

Compare Any Two of Anything in Power BI

I have been increasingly recording videos as my method of sharing tips and techniques for getting more out of Power BI. Some concepts are best documented (traditional blogging) and some are just easier for everyone if it is a video. That is the case again for today. Compare Any Two [...]Read More »

exceleratorbi.com.au

Hi Matt. Your solution works very well, thanks.
I had the following follow up questions and I would appreciate your help:

1. The source data will be refreshed daily. Do I need to prepare it as unpivoted table?
2. Is there a way to set the date slicers such that a user cannot input a ‘To’ date (comparison calendar) earlier than 'From' date?
3. Is there a way to set the slicers such that within a slicer, a user cannot select multiple dates

Once again, thanks for your help.

 

[smozgur]

Hi Matt. Your solution works very well, thanks.
I had the following follow up questions and I would appreciate your help:

1. The source data will be refreshed daily. Do I need to prepare it as unpivoted table?
2. Is there a way to set the date slicers such that a user cannot input a ‘To’ date (comparison calendar) earlier than 'From' date?
3. Is there a way to set the slicers such that within a slicer, a user cannot select multiple dates

Once again, thanks for your help.

The marked solution post has been switched accordingly.

@Bobstar - please create another thread for your new question(s).