Financial forecasting and history tables utilizing one slicer

[Anonymous]

Hi all,

I'm currently setting up a report to show my company's financial forecasting data. I've got all of the basics figured out, but there is one ask that I just can't wrap my head around how to do.

Currently, I have a stacked column chart that shows the value of our currently won work and any opportunities we are bidding split out by month. I have a slicer for this table that allows the team to look at a specific date range (next 3 months, 6 months, 1 year, etc). The team has asked for a second table that shows the values for the inverse date range (last 3 months, 6 months, etc) based on that first selection. They don't want to have to use a second slicer to get that information, but for it to be automatic when that first slicer is used. 

Based on my preliminary look through the forum it looks like it should be possible with a measure, but I just can't wrap my head around it and I don't have a great command of DAX 😅 Is this something I can do? And how would I go about doing it?

[DataInsights]

@Anonymous,

The article below describes a technique that you may find useful. The concept is to create a clone of your date table, and use DAX to filter the appropriate dates in the visual.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

[Anonymous]

I partially used this solution in trying to fix my issue, but utilizing a few other measures because I needed it to be dynamic on the Interval/start date/end date as I'm wanting the relative date slicer that controls my main table to control the interval of the second table. 

But the issue that I am still running up against is that while all of my measures give me the results I want, when I try to use them all together in another measure or table to actually get the set of dates it either completely fails on me or gives me a single date that doesn't fit any of the parameters. 

[DataInsights]

@Anonymous,

If you could share a sample pbix using one of the file services like OneDrive, it would assist in troubleshooting.