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

    Previous sales (month Year)

    David Nichols

      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?

      Thank you in advance

      Daniel

        • Re: Previous sales (month Year)
          Sunny Talwar

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

              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

              Sum({$<[MonthYear]={"$(=Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY'))"}>}[Invoive Total])


              Thank you if you can come up with anything else

              Daniel

                • Re: Previous sales (month Year)
                  David Nichols

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

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

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

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

                                  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

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

                                  Surely this should be just last max month

                                  and in relation

                                  last complete month i am switching to

                                  max -2

                                  Sum({$<[MonthYear]={"$(=Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY'))"}>}[Invoive Total])

                                   

                                  Should i be expecting it to work like this??

                                  TYIA

                                  Daniel

                      • Re: Previous sales (month Year)
                        Gysbert Wassenaar

                        Perhaps like this:

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

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

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

                        sum({$<[MonthYear]={'>=$(=Date(YearStart(Max(MonthYear),-1),'MMM-YYYY'))<=$(=Date(AddYears(Max(MonthYear),-1),'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)
                            David Nichols

                            Hi,

                            Thank you for the suggestion Gysbert.

                            When using the below i am getting error.

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


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


                            For my year to date i need to calculate jan to  end of complete last Month (May) for the period of 2015 and 2016

                            Thank you in advance if you can assist further. Thank you