9 Replies Latest reply on Apr 9, 2018 6:39 AM by daniel.andell

# Running 12 months from last invoice

I would like to have a running total as a line chart showing last 12 months of sale and compare to the last 12 months before that. I have created the calculation below and it works fine except that it is calculating from today. Lets say my last sales date is 15th of October then I would like the calculation to be from that date.

Another thing I would like to do is to make the calculation count 12 months back from that date not from month. So, I would like help to remake the calculation to take one year back. For example if it is the 15th of November I would like the calculation to look until the 16th of November last year.

Last thing I would like to do is to reverse the month depending on what month it is. For example, if last invoice date is February then I would like February to be shown first and the other months behind it. So that you see 12 months behind last invoice.

Thanks

IF (DATEDIFF('month',[Day],TODAY()))<=12 THEN [Sales amount] ELSE null END

• ###### 1. Re: Running 12 months from last invoice

Hi Daniel,

Kind regards,

Francesca

• ###### 2. Re: Running 12 months from last invoice

Thanks

Here is an example

• ###### 3. Re: Running 12 months from last invoice

Hi, Daniel

edit the axis

Result as below

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Running 12 months from last invoice

Thanks Zhouyi Zhang but there is one thing I would like your help to change.

I would like the two lines like in the example below. But I would like the chart to end with october since that is the last month of sales.

Is there a way to make this work dynamically so that the last month of sales is to the right and then all the rest behind?

• ###### 5. Re: Running 12 months from last invoice

Hi, Daniel

Create two calculation fields and set up the sorting for month

Below is the sample result.

Workbook attached, hope this could help

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Running 12 months from last invoice

Thanks a lot. Just one thing I dont fully understand. How do I access the menu below?

Doesnt matter where I click I still cant find it

• ###### 7. Re: Running 12 months from last invoice

Hi, Daniel

You can find the sort configuration from the drop down

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Running 12 months from last invoice

Zhouyi Zhang I am using this chart with actions so that the user can see it depending on product group etc.

Now i realize that the problem is that is sales are missing one or more months the calculation is not working.

Is there a way to remake the sorting key below to Always use last 12 months without filtering depending on the actions I use?

DATEDIFF('month',[Sales date from PY month and back],{ FIXED :MAX([Sales date from PY month and back])})%12

• ###### 9. Re: Running 12 months from last invoice

Any ideas how to solve this issue when all months are not in the filter?