How to display the full year budget while using a date slicer

[SueM]

 Hi there.

I am trying to display the budget for the full year in a table in which I have YTD and MTD budget and sales figures. I have a date slicer on this table (month and year).

I have tried to use the formula

Measure = CALCULATE(SUM('Internet Sales'[Sales Amount]),ALLSELECTED('Date'[Calendar Year]))

with my own date table, but the full year budget still pulls through as the budget for the month selected on the date slicer. 

Thanking you in advance.

[v-yuta-msft · Accepted solution]

Hi SueM,

You can try RELATED() function, modify your measure like below may achieve your requirement:

Measure =
SUMX (
 FILTER ( 'Internet Sales', RELATED ( 'Date'[Calendar Year] ) ),
 'Internet Sales'[Sales Amount]
)

Regards,

Jimmy Tao

[v-yuta-msft]

Hi SueM,

Does table Internet Sales and table Date have relationship with each other? If does, you may modify your measure like pattern below and check if it can meet your requirement:

Measure = CALCULATE(SUM('Internet Sales'[Sales Amount]),ALLSELECTED('Date'))

In addtion, if still can't work, could you share some sample data and charify more details about your requirement?

Regards,

Jimmy Tao

[SueM]

Hi Jimmy

Thank you for your help. I did try your suggestion, but it was still pulling through the budget for the current month, selected from the slicer. I tried the "ALL" in the formula, rather than "ALLSELECT", which then pulled through the total budget for all years.

My date table is not linked to the budget table, they are linked via a sales table (see ralationships below).

Below is the table I am working on, to show YTD budget and the budget for the full year (based on the date selected on the slicer). Currently it is showing the total budget for all years, and not the year being selected in the slicer.

The slicer slects the month and year. I need the "Full Year Budget" to be calculated on the year being selected, only.

Budget Table:

Sales Table: 

 Relationships:

I hope this clarifies things.

Regards,

SueM

[Greg_Deckler]

Sample data would help tremendously. 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

That being said, you might try something like:

Measure = 
VAR __Year = MAX('Date'[Calendar Year])
RETURN SUMX(ALL('Internet Sales'),[Year] = __Year)