Need a measure to return annual budget by GL Account that does not change with month slicer

[AllysonV]

I need to create a measure that will return the total annual budget for a GL Account. The budget totals are included in my FACT table along with the expense data. The budget data lines have a transaction date (last day of respective month) and the budget total is in a separate column from the expense total.

I have a table that changes based on a year and month slicer that lists:

GL Account
Total expense = "sum(FACTtable debit)+sum(fact table credit)"
Total budget = "sum(FACTtable budget)"
Total variance = "[total expense]-[total budget]"
YTD expense ="CALCULATE([Total Expense],DATESYTD(Dates[Date]))"
YTD budget ="CALCULATE([Total Budget],DATESYTD(Dates[Date]))"
YTD variance ="[YTD Expense]-[YTD Budget]"

I want to add:
Annual budget = static amount that doesn't change with month slicer
Remaining budget =" [annual budget] - [YTD expense]"

I believe I need to change the filter context but I am not sure how.

[jdbuchanan71]

hello @AllysonV 

Give this a try.  The BudgetMonth VAR is in there just to keep the annual budget from running into months with no data.

Annual budget = 
VAR BudgetMonth = [Total budget]
RETURN 
IF ( NOT ISBLANK ( BudgetMonth ),
    CALCULATE (
        [Total budget],
        ALL ( Dates )
    )
)

I uploaded my sample .pbix here https://www.dropbox.com/s/0kfsv81ujdooibh/AnnualBudget.pbix?dl=0

[AllysonV]

I entered your variable information as Annual Budget and still received the same results with the table set-up the way I want to view it.  The measure is still being filtered by the month slicer.  I need to know how to break or switch the filter context so I get the annual total by year for the Annual Budget measure.

Budget to Acutal by GL Account by Month and Year

[jdbuchanan71]

That is what this code does.

CALCULATE (
        [Total budget],
        ALL ( Dates )
    )

It removes the filter context from the date table so the measure returns the whole budget.  Is your month filter from something other than your Dates table?

[AllysonV]

I see what I did wrong... I selected the transaction date column in the dates table.

Annual Budget = 
VAR AnnualBudget = [Total Budget]
 RETURN
  IF(Not ISBLANK(AnnualBudget),
   CALCULATE(
     [Total Budget],
       ALL(Dates[Date])))

I have changed the measure to

Annual Budget = 
VAR AnnualBudget = [Total Budget]
 RETURN
  IF(Not ISBLANK(AnnualBudget),
   CALCULATE(
     [Total Budget],
       ALL(Dates)))

and now I have a total in Annual Budget for all Budget amounts for both 2018 and 2019.  So we are halfway there.  I'm hoping there is an easy fix so I only get the total annual budget amount for the year selected in the slicer.

[jdbuchanan71]

Yep, we can fix that.  We just need to read the year and bring it back into our filter.

Annual budget = 
VAR AnnualBudget = [Total budget]
VAR ActiveYear = VALUES ( Dates[Year] )
RETURN 
IF ( NOT ISBLANK ( AnnualBudget ),
    CALCULATE (
        [Total budget],
        ALL ( Dates ),
        ActiveYear
    )
)

Or we can use ALLEXCEPT.

Annual budget = 
VAR AnnualBudget = [Total budget]
RETURN 
IF ( NOT ISBLANK ( AnnualBudget ),
    CALCULATE (
        [Total budget],
        ALLEXCEPT ( Dates, Dates[Year] )
    )
)

I like the first one because it keeps years from rolling together if you pick more than 1 and are showing the dates.

[AllysonV]

What does "ActiveYear" refer to in your data?  I added ActiveYear to my measure and I get the below error:

Here is a list of the fields in my Date table:

[jdbuchanan71 · Accepted solution]

It's an additional VAR I added to the measure:

[AllysonV]

Oops missed that in the previous post.  Adding the VAR ActiveYear did the trick!  Your help is much appreciated.