topic Filter forcasted dates based on slicer selections in Developer

Filter forcasted dates based on slicer selections

jj6291 — Sun, 14 Nov 2021 21:40:10 GMT

I hae a dataset where we have forecasts of the price of something based on every day of the year. i.e the forecasted 2022 price based on the 3rd of march, now based on the 4th of march etc.

The data comes through an oracle query and updates live, gathering a rolling 5 year period. And we have two date variables, the current dates, and the forecast period

What I want to do is that when you choose the year to look at the forecasts for, using a slicer, I want to only plot the forecasted data which is >= than the selected date.

So for example, the current dates go from 2016-2021 so the forecasts go from 2017 to 2022. When you choose to look at the current dates of 2018, I don't want to display forecasts from 2017 and so on, because the past data is known and not interesting.

My current hack is to have two slicers where the end user would have to fiddle with both dates, but that is not a clean and intuitive solution. I have included a screenshot of some of the plot where the straight lines are past years of data that I don't want displayed.

Re: Filter forcasted dates based on slicer selections

VijayP — Mon, 15 Nov 2021 10:27:43 GMT

jj6291

Use this formula
IF(DateDim[Year]<SELECTEDVALUE([DATEDIM[Year]), BLANK(),
required measure for Forecast) let me know if that works . Else share the sample data without any classified information.

Re: Filter forcasted dates based on slicer selections

Anonymous — Thu, 18 Nov 2021 01:44:06 GMT

Hi jj6291,

It sounds like you want to achieve a custom filter effect based on slicer selections instead of filter effect.
If that is the case, I'd like to suggest you create an unconnected date table as the source of the slicer then you can write the measure expression and use it on your visual filter level to apply filter effects.

Applying a measure filter in Power BI - SQLBI

Deep dive into measure filters in Power BI Desktop - SQLBI

Regards,

Xiaoxin Sheng

Re: Filter forcasted dates based on slicer selections

jj6291 — Sun, 21 Nov 2021 22:09:21 GMT

Hi, thanks for the suggestion. I ended up doing something similar. I made a new table and set columns up for overlapping periods and then made a 3rd table where I unpivoted the data manually. I think it was a bit overkill