Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Trying to sum full year but getting error message

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

 

 

Need Help
  • Sean's avatar
    Sean
    9 years ago
    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 )
    )

     

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

     

    Regards

11 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Sean,

       

      Thats Great!

       

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

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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 )
    )