Forum Discussion
Dynamic filtering in measure with a variable
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
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:
- 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:
- 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.
3 Replies
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
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:
- 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:
- 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.
Thanks!
That works like a charm.
