Trying to sum full year but getting error message

[Anonymous]

I have the years selected on a slicer and it am trying to sum full year budget using

full year = CALCULATE(SUM(Budgets[amount]),'Calendar$'[Date].[Year])

but I am receiving this error message.

An unexpected error occurred (file 'xlname.cpp', line 388, function 'XLNamePair::NormSubtree').An unexpected error occurred (file 'xlname.cpp', line 388, function 'XLNamePair::NormSubtree').

Help

Thanks

[Sean · Accepted solution]

Full Year 2016 =
    CALCULATE (
        SUM ( Budgets[Amount] ),
        FILTER ( ALL ( 'Calendar$' ), 'Calendar$'[Date].[Year] = 2016 )
    )

So if your Slicer is from the Calendar table use the above!

If not this should work either way...

Full Year 2016 ALT =
    CALCULATE (
        SUM ( Budgets[Amount] ),
        FILTER ( ALL ( 'Budgets' ), 'Budgets'[budget_code] = 2016 )
    )

[Anonymous]

Wish I could add two answers to this as it took a combination of both answers.

Needed to use the Filter ALL otherwise it would change on the month selected.

Full Year  = var y = MAX('Calendar$'[Year]) return 
    CALCULATE (
        SUM ( Budgets[Amount] ),
        FILTER ( ALL ( 'Calendar$' ), 'Calendar$'[Date].[Year] = y )
    )

[Sean]

@Anonymous Are you trying to get the current full year?

full year =
CALCULATE (
    SUM ( Budgets[amount] ),
    FILTER (
        ALL ( 'Calendar$' ),
        'Calendar$'[Date].[Year] = MAX ( 'Calendar$'[Date].[Year] )
    )
)

If you want to get the total for several years selected in a Slicer you may be over thinking this a simple sum would do this.

EDIT: Or wait I think you may be trying to achieve this...

full year =
CALCULATE ( SUM ( Budgets[amount] ), ALLSELECTED ( 'Calendar$' ) )

[Anonymous]

Hi Sean,

Thats Great!

Yes I am trying to sum the year selected in the slicer... will give that a try.

Thanks!

[Anonymous]

Thanks Sean I have tried that but thast doesnt work ...

Becasue the user can also select the month as well as the year i somehow need to reference the slicer?

I have tried

Full Year =
CALCULATE ( SUM ( Budgets[amount] ), DATESBETWEEN('Calendar$'[Date], STARTOFYEAR('Calendar$'[Date]),ENDOFYEAR('Calendar$'[Date])))

but that doesnt work either.

[v-ljerr-msft]

Hi @Anonymous,

According to your description, you may need to use the formula below to add a calculate column called "Year" in your "Calendar$" table first.

Year = YEAR ( 'Calendar$'[Date] )

Then, you should be able to use the formula below to create the "Full Year" measure.

Full Year =
    VAR y =
        MAX ( 'Calendar$'[Year] )
    RETURN
        CALCULATE ( SUM ( Budgets[amount] ), YEAR ( 'Calendar$'[Date] ) = y )

Regards

[Anonymous]

Thanks Jerry, i can see that working but the problem is we have transactions in there for next year.

Would MAX still bring in 2016? or 2017?

Didnt know you could have variables in DAX.. cool

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Thanks Jerry, i can see that working but the problem is we have transactions in there for next year. 

Would MAX still bring in 2016? or 2017?

---

What columns are you using for the "Year" Slicer and "Month" Slicer? If the columns used for the Slicers are all from your "Calendar$" table(and you should use date column of the"Calendar$" table for both your Slicers in this scenario), then the formula I provided above will sum the full year depends on your selection of the "Year" Slicer. For example, if you select "2016" for "Year" Slicer, then the MAX will bring in 2016, and the measure will sum full year of 2016.

Regards

[Anonymous]

No matter what I do even

Full Year = 
CALCULATE ( Sum(Budgets[amount]),Budgets[budget_code]=2016)

Only returns the month selected in the slicer...I need to override it somehow?

[Anonymous]

Anyone?

[Sean · Accepted solution]

Full Year 2016 =
    CALCULATE (
        SUM ( Budgets[Amount] ),
        FILTER ( ALL ( 'Calendar$' ), 'Calendar$'[Date].[Year] = 2016 )
    )

So if your Slicer is from the Calendar table use the above!

If not this should work either way...

Full Year 2016 ALT =
    CALCULATE (
        SUM ( Budgets[Amount] ),
        FILTER ( ALL ( 'Budgets' ), 'Budgets'[budget_code] = 2016 )
    )

[Anonymous]

Thanks Sean, will give that a try, take it there is no way to get it to say 2016 automatically?