Dynamic filtering in measure with a variable

[tjalleph]

Hi all,

I'm hoping you can shed some light on a behavior in measures that I can't seem to understand. 

Context

I'm trying to visualize budget, sales forecast and actuals on a monthly basis. From the selected months in a slicer (say Jan-March), I'd like to show budget and sales forecast for the next three months (Apr, May, Jun) as well. The three numbers are in different tables and are connected through a Calender table.

In table-form, the result should be:

Month	Budget	Forecast	Actuals
Jan	100	120	110
Feb	120	110	100
Mar	120	123	128
Apr	120	133
May	120	105
Jun	120	98

However, if I try to dynamically find the last month number of actuals and add 3, and use that calculate forecast and actuals, i only get a resulting table for the months 1-3.

Month	Budget	Forecast	Actuals
Jan	100	120	110
Feb	120	110	100
Mar	120	123	128

I guess the slicer is the issue here, how can I circumvent this?

Thanks for the help!

Tjalle

[tjalleph]

Thanks!
That works like a charm.

[v-robertq-msft · Accepted solution]

Hi, @tjalleph 

According to your description, I can roughly understand your requirement, then I created a data model to make a test based on your sample data, you can try my steps:

1. Create a calculated table for the slicer, which is the same as the calendar table:

Slicer = 'Calendar'

Then keep the table don’t have any relationship with others:

2. Create three measures in the table:

Budget =

var _minmonth=MINX(ALLSELECTED(Slicer),[Month number])

var _maxmonth=MAXX(ALLSELECTED(Slicer),[Month number])

return

IF(MAX('Calendar'[Month number])>=_minmonth&&MAX('Calendar'[Month number])<=_maxmonth+3,SUM(Budget[Budget]),BLANK())

Forecast =

var _minmonth=MINX(ALLSELECTED(Slicer),[Month number])

var _maxmonth=MAXX(ALLSELECTED(Slicer),[Month number])

return

IF(MAX('Calendar'[Month number])>=_minmonth&&MAX('Calendar'[Month number])<=_maxmonth+3,SUM(Forecast[Forecast]),BLANK())

Actuals =

IF(MAX('Calendar'[Month]) in SELECTCOLUMNS('Slicer',"1",[Month]),SUM(Actuals[Actuals]),BLANK())

Then create a slicer and a table chart to place them like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@tjalleph , if you select a small duration and you want to show a larger duration you need to have an independent date table.

refer my video on same 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI