10 Replies Latest reply: Jun 30, 2016 9:30 PM by Sunny Talwar

# Previous sales (month Year)

Hi,

I am currently manually amending our tables to display data required:

Last Month Sales     -                          sum({\$<[MonthYear]={'Apr-2016'}>}[Invoive Total])

Current month (last year)                     sum({\$<[MonthYear]={'May-2015'}>}[Invoive Total])

Current month (this year)                     sum({\$<[MonthYear]={'May-2016'}>}[Invoive Total])

YTD (last year)                                   =sum({\$ <MonthYear={">=Jan-2015<=May-2015"}>} [Invoive Total])

YTD                                                  =sum({\$ <MonthYear={">=Jan-2016<=May-2016"}>} [Invoive Total])

Can anybody be my hero and let me know using (Max) how i can make this dynamic?

Daniel

• ###### Re: Previous sales (month Year)

Try these:

Last Month Sales     -                          Sum({\$<[MonthYear]={"\$(=Date(AddMonths(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[Invoive Total])

Current month (last year)                    Sum({\$<[MonthYear]={"\$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[Invoive Total])

Current month (this year)                     Sum({\$<[MonthYear]={"\$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>}[Invoive Total])

YTD (last year)                                   =Sum({\$ <MonthYear={"\$(='>=' & Date(YearStart(Max(MonthYear), -1), 'MMM-YYYY') & '<=' & Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>} [Invoive Total])

YTD                                                  =Sum({\$ <MonthYear={"\$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}>} [Invoive Total])

• ###### Re: Previous sales (month Year)

Thank you so Much Sunny T,

I still have issue where the figures are not working for

Current month (last year)     Sum({\$<[MonthYear]={"\$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[Invoive Total])

YTD (last year)            =Sum({\$ <MonthYear={"\$(='>=' & Date(YearStart(Max(MonthYear), -1), 'MMM-YYYY') & '<=' & Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>} [Invoive Total])

I see you are using the max monthyear.

Using your new syntax the figures are slightly higher than anticipated using the fixed expressions.

Are the above calculating last complete month?

To make previous month figures correct i actually made MonthYear -2

Thank you if you can come up with anything else

Daniel

• ###### Re: Previous sales (month Year)

Hello again Sunny,

I would like to thank you again first for your time and expertise. You have helped so much already.

Re you suggestions for the previously mentioned. Please let me show the the fixed month vs dynamic you have suggested and where the differences are in the results. I am really hoping you can assist further:

The syntax given by you makes the total higher for each of the below compared to the fixed month year results.

Thank you in advance if you can help further with the below

Current month (last year)                     sum({\$<[MonthYear]={'May-2015'}>}[Invoive Total])

i get a difference when using

Current month (last year)      Sum({\$<[MonthYear]={"\$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[Invoive Total])

YTD (last year)                                   =sum({\$ <MonthYear={">=Jan-2015<=May-2015"}>} [Invoive Total])

i get a difference when using

YTD (last year)               =Sum({\$ <MonthYear={"\$(='>=' & Date(YearStart(Max(MonthYear), -1), 'MMM-YYYY') & '<=' & Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>} [Invoive Total])

YTD                                                  =sum({\$ <MonthYear={">=Jan-2016<=May-2016"}>} [Invoive Total])

i get a difference when using

YTD                      =Sum({\$ <MonthYear={"\$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}>} [Invoive Total])

• ###### Re: Previous sales (month Year)

Would you be able to check if date is truly a date field and not text? Here are some links to read about dates in general:

Get the Dates Right

Why don’t my dates work?

Once you have made that fix, next would be to make sure that you use the dates correctly in set analysis. How have you created MonthYear in the script? Did you use MonthName function or Date(MonthStart()) function? This part is important because set analysis will only work if you have same format on the LHS and RHS of your set analysis equation. Read about dates in set analysis here:

Dates in Set Analysis

• ###### Re: Previous sales (month Year)

Thank you,

I will check these out but yes... I did create date on load, see below.

[Issue Date],

Year([Issue Date]) as [INV Year],

Month([Issue Date]) as [Inv Month],

Day([Issue Date]) as [Inv Day],

Date(monthstart([Issue Date]), 'MMM-YYYY') as MonthYear,

I will certainly check out the other posts.

Thank you

• ###### Re: Previous sales (month Year)

Since you use Date() function, the field is an actual date field. So you can avoid reading the first two links. With regards to the difference you are seeing, can you confirm that the one not using the Max() function is actual correct? Can it be that the original expression was somehow giving incorrect numbers and new one gives the correct result?

• ###### Re: Previous sales (month Year)

Thank you,

I am confident of the 'fixed figures' as this is using the accountants previous reports for me to compare to. they are right to the penny.

I think  it worth me mentioning again that for the:

current complete month (MAY) we use max month -1

Surely this should be just last max month

and in relation

last complete month i am switching to

max -2

Should i be expecting it to work like this??

TYIA

Daniel

• ###### Re: Previous sales (month Year)

I think this should work... Have you tried to check if it works?

• ###### Re: Previous sales (month Year)

Perhaps like this:

sum({\$<[MonthYear]={'\$(=Date(Max(MonthYear),'MMM-YYYY'))'}>}[Invoive Total])

sum({\$<[MonthYear]={'>=\$(=Date(YearStart(Max(MonthYear)),'MMM-YYYY'))<=\$(=Date(Max(MonthYear),'MMM-YYYY'))'}>}[Invoive Total])

If youre MonthYear field contains text values instead of dates then you should create a MonthYear field that contains dates in the script first.

• ###### Re: Previous sales (month Year)

Hi,

Thank you for the suggestion Gysbert.

When using the below i am getting error.