Latest Enterprise DNA Initiatives

Totals by year - ALL + Dates table

Hello,

I’m working on the topic of Budgeting and Forecasting and trying to work out a Sales seasonality per month. I am using the dataset provided on the site for the course (Mini serie).

I have the following Sales table:

The following Dates table:

And the following Products table:
image

The data model is as stated in the course, as follows:

I have set up the Toal sales measure as follows:

Now, what I want is to have the total Sales value for each year, for all rows of the same year. For example, the total for 2015 is 59’394’341 and I want that value for all the rows for the year 2015. that would enable me to calculate January 2015 seasonality, i.e. 6’071’955 / 59’394’341 = 10.2%.

I have then set up the following measure, expecting it would work, but it doesn’t:


The ALL function with the Dates table doesn’t seem to be working.

I don’t understand why because if I do the same for the products (not to calculate seasonality, but just as an example), I get the right totals:
image
the ALL function works with the Products table.

I am missing something fundamental I believe but cannot get what. Thank you for your help.

Pascal

Hello,

I’ve actually found the answer on the web. and getting the correct result nom with the following formula, BECAUSE AS THE MonthName IS SORTED BY MonthOfYear IN THE DATES TABLE, IT HAS TO BE ADDED IN THE FORMULA.

Thank you, hope it helps other people!

Pascal

4 Likes

@Pbovet I am glad that you found a solution, I wrote a solution for a similar question a few days ago, you might wanna take a look.

1 Like

Hi @Pbovet, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

@AntrikshSharma Thanks for your thorough explanation. It is so useful.

1 Like