Use fix value for all month

[Pfoerster]

Hello, 

I am working on a report, where it is necessary, to use for my plan-period the value of december for the whole year. 

How can I do this? 

Currently, due to my source-table, the measure for calculating the budget volumes is:

StockEndBud = CALCULATE(SUM('Raw Data'[Stock End]),FILTER('Raw Data','Raw Data'[Scenario]="Budget"))

When I now use also a Filter for month ('Raw Data'[Month]="December"), I only receive in my visual a value for december, and all other month are blank. How can I solve this? 

[PaulDBrown]

@Pfoerster 

Try:

December value = CALCULATE([StockEndBud]
FILTER(ALL(Raw Data),
Raw Data[month] = "December"))

(BTW, you should be using a Date Table)

[Pfoerster · Accepted solution]

I got it 😀:

First: I build up an extra Date-Table (this solves nearly 80% of my problems!), and then, not used the "All" but "Allselected" formular.

StockEndBud = CALCULATE(SUM('Raw Data'[Stock End]),FILTER(ALLSELECTED('Raw Data'),'Raw Data'[Scenario]="Budget"&&'Raw Data'[Month]=SELECTEDVALUE('Month'[Month])))

Now, all other slicers seems to work. Thank you for all your input! 

[v-jayw-msft]

Hi @Pfoerster ,

Create a calendar table with month column and use this column as slicer.

Modify your formula as below.

StockEndBud = CALCULATE(SUM('Raw Data'[Stock End]),FILTER(ALL('Raw Data'),'Raw Data'[Scenario]="Budget"&&'Raw Data'[month]=SELECTEDVALUE('CALENDAR'[Month])))

 

If i misunderstood your meaning please let me know.

Best Regards,

Jay

[Pfoerster]

Hi,

yes, your formular is working. I got the value for December for each month. Thank you. 

But: I am using in my report a lot of different slicers (in example: Year, Article, and a lot of more). When I am using your formular, the "All" is deleting all selections and then adding the new Filter of the Scenario and the selected month. 

How can I still use the rest of the slicer selection for the budgeted values? 

[amitchandak]

@Pfoerster , need some sample data for that.

There is a way if the target is a month value https://www.youtube.com/watch?v=yPQ9UV37LOU

or try this with time intelligence and date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

refer this month vs last month in

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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, I want to create this kind of chart. For light grey and light blue line, the values should be the december ones (so, no increase for the light blue line is shown, but a stable line). 

The Problem: my raw data is, due to the weak source system, a calculated figure, and I do not want to adjust this Import but looking for a way, to tell the PowerBI Measure, to use the december value for all month.

[Greg_Deckler]

@Pfoerster - Well, you are filtering the data to December so that's kind of to be expected. If you want to avoid that, you probably need to use ALL or ALLEXCEPT somewhere. 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.