Dynamic X Axis based on Slicer using Dynamic table or RANKX

[prabhatnath]

Hi Friends,
I have a dataset (Link to the dataset as Google sheet) that has Sprint Name, Start Date, and End Date.
The Sprint Name is used as Slicer and Hours Worked (Estimate and Completed columns) are shown as Clustered Column Chart for the selected Sprint Name. We can't allow users to select multiple sprint names.

The sample PBIX file is shared here for reference. 

If the user selects "Sprint 2404" then the chart should show data for Sprint 2404 + Sprint 2403 + Sprint 2402.

If the user selects "Sprint 2403" then the chart should show data for Sprint 2403 + Sprint 2402 + Sprint 2401.

Basically 2 (can be configured for 3 or 4 etc, if available) sprints before the selected sprint based on the Start Date/End Date.

I was thinking to have a Custom Column used to RANK the rows using RANKX for Sprints based on Start Date/End Date, but not sure how shall I do that.

or I was thinking of another option to have a Dynamic Table that will have One Column - Sprint Name (the selected one) + Previous Sprint +  Previous to Previous Sprint (Based on StartDate/End of the Selected Sprint). And we use this dynamic table in X-Axis and show data from the original Data Set.

These were my thoughts, but not sure if this is possible. Please suggest.

Thanks,

Prabhat

[johnt75]

Adding an index column to rank the sprints is a good idea, either using the RANKX function as you suggest or you could do it in Power Query by sorting the sprints by date and then adding an index column.

Either way, you could then create a new table like

Sprint Slicer =
SELECTCOLUMNS(
	GENERATE(
		SUMMARIZE( 'Sprint', 'Sprint'[Sprint Name], 'Sprint'[Index]),
		VAR CurrentIndex = 'Sprint'[Index]
		RETURN GENERATESERIES( MAX( CurrentIndex - 2, 1), CurrentIndex)
	),
	"Sprint", 'Sprint'[Sprint Name],
	"Sprint number", [Value]
)

Create a relationship from the new table to your sprint table, so that the new table filters the sprint table, and use the column from the new table in your slicer.

[prabhatnath]

Thank you for your help on the Dynamic table.
As I am new I need guidance on using the RANKX function to add the index column.

Reg. Power Query to sort the sprints by Date: 
There are multiple rows (all work items) with the same sprint start date and Sprint Name as they all belong to the same Sprint, so adding an Index after sorting Start Date or Start Date + SprintName will not work. So the Index/Rank should rank all Work items/rows belonging to a Single Sprint with one number .

So the data should have Index like below:

Thanks,

Prabhat

[johnt75]

Try

Sprint index =
RANKX (
    ALL ( 'Table'[Sprint], 'Table'[Start date] ),
    'Table'[Start date],
    ,
    ASC
)

[prabhatnath]

Thank you for the RANKX and it is doing exactly what I was looking for.
Once this Index is added I tried your virtual table code as below but unfortunately, the table value is not getting updated based on the Sprint Slicer selection. Below is the Screenshot for your reference.

If the user selects "Sprint 2404" then the chart should show data for Sprint 2404 + Sprint 2403 + Sprint 2402. This means the new table should have Sprint Values: 2404, 2403, and 2402.

If the user selects "Sprint 2403" then the chart should show data for Sprint 2403 + Sprint 2402 + Sprint 2401. And here the new table should have Sprint Values: 2403, 2402 and 2401.

I think we should fill the new Dynamic Table with:
Sprint Name that was selected by the user in the Slicer +
Append Sprint Name from the Sprint table that has Index - 1 of the Selected Sprint Index.
Append Sprint Name from the Sprint table that has Index - 2 of the Selected Sprint Index.
This is just my thought but not sure of that is possible. This will ensure the Dynamic table has updated Sprint values based on Slicer Selection and can be used as X Axis.

Thanks,

Prabhat

Velocity Sprints = 
SELECTCOLUMNS(
	GENERATE(
		SUMMARIZE( 'Sheet1', 'Sheet1'[Sprint Name], 'Sheet1'[Sprint Index]),
		VAR CurrentIndex = 'Sheet1'[Sprint Index]
		RETURN GENERATESERIES( MAX( CurrentIndex - 2, 1), CurrentIndex)
	),
	"Sprint", 'Sheet1'[Sprint Name],
	"Sprint number", [Value]
)

[johnt75]

You need a relationship from the new table to the existing one. You should use the column from the new table in the slicer and the columns from the existing table in the other visuals

[prabhatnath]

I forgot to have the relationship set up. I Tried but got the below error while setting the relation. Similar error for any Cardinality I select. I am not an expert so might be making mistakes.

Also, I was thinking we should fill the new Dynamic Table with:
Sprint Name that was selected by the user in the Slicer +
Append Sprint Name from the Sprint table that has Index - 1 of the Selected Sprint Index.
Append Sprint Name from the Sprint table that has Index - 2 of the Selected Sprint Index.
and then setup the relationship. This will ensure the Dynamic table has updated Sprint values based on Slicer Selection and can be used as X Axis.

Thanks,

Prabhat

[johnt75]

The message you got when trying to create the relationship isn't an error, its a warning. Many-to-many relationships are normally not the best, hence the warning, but its OK in this situation. Change the cross filter direction to single so that the new table filters the sprint table.

I don't think we need to add anything else to the new table. Its only purpose is to be used in the slicer so that it filters the main sprint table to just the selected sprints. You can use the sprint name column from the main sprint table in all your visuals other than the slicer.

[prabhatnath]

Thanks, John for the clarification/help.

I tried the same but seems the chart does not show 2 previous sprint details of what is selected in the Slicer. Here I have uploaded the updated PBIX for your review.  It will be a great help if you can quickly review and suggest where I am doing wrong.

Thanks,

Prabhat  

[johnt75]

You have the relationship on the wrong columns. You need it from Sprint Index to Sprint number.

[prabhatnath]

Hi John,
Thank you for the hint on the relation column it worked as expected for the Chart, where the chart shows the previous 2 sprint data based on the relation, but Unfortunately, I realized that other parts of the report do not work as expected.

Reason:

Rest all visuals in the report depending on the main table or the selected Sprint Name, but as the main table gets filtered with "current sprint plus previous 2 sprints" all other visuals are impacted.

Ideally the "Column Chart" should be shown based on the "Selected Sprint and the previous 2 sprints" but rest of all visuals should depend on the "main table or only with the Selected Sprint". 

Please suggest.

Thanks,

Prabhat 

[prabhatnath]

Hi,  johnt75 or any other experts here. Please guide me in this.
Thanks,

Prabhat

[prabhatnath]

Hi Friends,
I have uploaded an updated PBI file here and looking for any help.
You may go ahead and discard all logic and suggest any new way to get that.

Basically, I have data that has Sprints column that is used in a slicer (single select only) and many visuals in the report are based on the Sprint that is selected.

But I have a requirement where the Column Chart should show data for the selected Sprint and the Previous 2 Sprints. The previous 2 sprints can be determined based on the Start Data and End Date.

Please suggest.
Thanks,
Prabhat Nath