Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
Slicer = 'Calendar'
Then keep the table don’t have any relationship with others:
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.
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:
Slicer = 'Calendar'
Then keep the table don’t have any relationship with others:
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |