Slicers and overruling them

[Pfoerster]

Hello,

I need help with Slicers:

I have build up a report, where I have a bar chart with End Stocks per different planning Scenario (so far, so good).

I now want, that the user is selecting a month via a Slicer to see the Stocks per month. So I build up this slicer on another page of the report, where I have a lot of slicers for selecting End Stock information. Also this is working well, but now, the problem begins:

Until now, the user is getting (as example) the End Stock for the Scenario "Budget" and "Actual" for the selected month (= June). But: due to the fact, that in budget process, only the Year-End Stock is planned, the report should show for all planning Scenarios always the December End-Stock.

So: How can I build up a Measure for the End Stock, which is overruling the Month-Slicer of the report? I have tried to use the "Removefilters" function of DAX, but without success.

[amitchandak]

@Pfoerster , I did not get it completely.

but if you put a value at end of year, you can get it for all dates using -closingbalanceyear  (You need date calendar)

-https://docs.microsoft.com/en-us/dax/closingbalanceyear-function-dax

and for complete next year using openingbalanceyear

-https://docs.microsoft.com/en-us/dax/openingbalanceyear-function-dax

Can you share sample data and sample output in table format?

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Pfoerster]

As result of the report, I want to get this bar diagram:

On the left, you see the End-Stock (December) of Budget, then, next to the light blue, it is an updated Budget (also December), then (dark grey), you see Actual figures (June) and on the right, Optimal Stock (June).

Now, the User has the oportunity to filter the current month of the report (so, instead of June, he can swith back to March for example). As result, the dark grey and the dark blue bar will show the values of End of March, while Budget and Updated Budget stick to december values, because in the source, for planning periods, only the december values are reliable.

Currently, when I use the slicer (and switch to march), also Budget and Updated Budget is showing calculated March figures, which do not have any operational sence.

[v-lili6-msft]

hi  @Pfoerster 

For your case, you may need to use "ALL" function to create a measure.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If you still have problem, please share your sample pbix file and your expected output.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Pfoerster · Accepted solution]

Problem solved:

I build up a new Slicer, based on a new Table, where I only named up the Month.

After that, I build a new Measure, called DimMonth = Selectedvalue(Month[Month])

Then, it is easy to calculate the current stocks based on the month slicer by using a Filter which filtered the month column equal to DimMonth.