Forum Discussion
Helper IIIUse fix value for all month
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:
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!
7 Replies
- Greg_Deckler
Community Champion
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. - amitchandak
Super User
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-64b0b4a4090eTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
PfoersterAs 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.
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?
PaulDBrownTry:
December value = CALCULATE([StockEndBud] FILTER(ALL(Raw Data), Raw Data[month] = "December"))(BTW, you should be using a Date Table)
- Pfoerster
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!
