1 2 Previous Next 16 Replies Latest reply on Jul 31, 2017 11:28 PM by mahesh p

# Previous months sales values as a reference line

I have a case where I am building  a view where in I will be showing only this months sales value in it. I want to show the previous months sales value for each day in the current month in the view as a reference line.

I have been trying to get this done with using different things (window calculations, table calculations) but I feel I am doing something wrong or I am going entirely in the opposite direction.

Can someone please help me with this? Also, I don't want to use any parameters. The current month value changes dynamically and I want to show previous months data based on it.

Any help is truly appreciated.

Okechukwu Ossai

• ###### 1. Re: Previous months sales values as a reference line

Previous Month Sales From Current/Max Month:

SUM(IIF(DATETRUNC('month', [Date]) = DATEADD('month',-1,DATETRUNC('month',{FIXED: MAX([Date])})), [Sales], 0))

Since it is LOD with Fixed values, it will always give you monthly sales of last month. You can add this as dual axis with your current sales

• ###### 2. Re: Previous months sales values as a reference line

Hey Prayson, thanks for the reply. It does not work as I am getting 0 for Previous month value.

This is what I am getting.

Thanks

• ###### 3. Re: Previous months sales values as a reference line

It works as designed  it give last months value will setting zero to all other, since I thought that was what you were after! And I was wrong.

To get Previous month values, you can use

LOOKUP(SUM([Sales]),-1)

If you want Zero where there is no previous month, you can add

ZN(LOOKUP(SUM([Sales]),-1))

or if you want to return itself, if no previous month:

IFNULL(LOOKUP(SUM([Sales]),-1), SUM([Sales]))

Let me know if this works

• ###### 4. Re: Previous months sales values as a reference line

Hey I want the view to look like :

Current Month : Current Months Sales :  Previous Months Sales

• ###### 5. Re: Previous months sales values as a reference line

Like this? I have attached .twbx. I used Global SuperStore data that has december 2014 as it maximum date (current date)

How I did it:

1. I place Month-Year Order Dates to Columns

2. Sales and Lookup same of sales one step backwards to columns

3. Filter data to current month with LAST()=0

4. Transpose the view

• ###### 6. Re: Previous months sales values as a reference line

Yes, like this only but in my view I will have only current month present and I want to show previous month value in tool tip or as a separate measure and I am not sure if Lookup helps in what I want to achieve.

This is how the view would look:

Current Month : Current Months Sales :  Previous Months Sales

• ###### 7. Re: Previous months sales values as a reference line

Like this?

If not, can you offer a sample dataset and if you can use a MS Paint to draw what you want it to look like

• ###### 8. Re: Previous months sales values as a reference line

Hi,

another way to address this, are you looking for something like this?

Please find attached workbook for reference.

Thanks,

Sunil

1 of 1 people found this helpful
• ###### 9. Re: Previous months sales values as a reference line

How did you limit the data without adding any filter to the view?

• ###### 10. Re: Previous months sales values as a reference line

Hi,

calculated current month in calculated field and showed it in colors pill.

selected Hide option to hide previous months data.

• ###### 12. Re: Previous months sales values as a reference line

Yes, how did you achieve this. Can you please let me know?

• ###### 13. Re: Previous months sales values as a reference line

On the previous month flag color legend, right click on P (orange color) and select hide.

• ###### 14. Re: Previous months sales values as a reference line

I used LAST()=0 and set it to true

1 2 Previous Next