Trouble with Addmonths formula

[#1]

Sum ({$<[Bill Date]={"$(< (addmonths(MonthStart(Today()),-1,0)))"}>}[Bill FC Value])

My goal is to create a bar chart by month for each of the last twelve months on a rolling basis.  I understand from a previous posting the way to do that is to have an expression for each month using the addmonths formula and step back -1, -2, -3...-12 months.

Please help with the formula, or if there's a better way to accomplish the same results, please let me know.

Thank you!

My data looks like:

Bill Date	Bill FC Value
11/30/2015	4353.95
11/30/2015	4603.05
11/30/2015	6960.26
11/30/2015	22772.74
11/25/2015	551.57
11/25/2015	735.43
11/25/2015	8588.16
11/25/2015	11954.88
11/25/2015	18066.96
11/25/2015	55401.6
11/25/2015	83349
11/23/2015	6839.17
11/19/2015	1913.83
11/19/2015	3435.1
11/19/2015	3500

[JonnyPoole]

Try this. Tried to space out the syntax to follow better

Sum (

          {$<

               [Bill Date]=

               {"=<$(

                    =addmonths(MonthStart(Today()),-1,0))

                    )

               "}

          >} 

          [Bill FC Value])

[marcus_sommer]

The syntax isn't quite correct, try it this way:

Sum ({$<[Bill Date]={"<$(=addmonths(MonthStart(Today()),-1,0))"}>}[Bill FC Value])

I think also this will be helpful: Calculating rolling n-period totals, averages or other aggregations

- Marcus

[nico_ilog]

Hi Craig,

Last 12 months rolling as in MAT "Moving Annual Total".

I had days of sitting with that, but figured it out this way.

The Preparation:

I have 3 variables:

1. vMaxDate value is:=Date(max(Bill Date))

2. vMATTYEnd = =Date(vMaxDate) |--> This vMaxDate is set above in #1
3. vMATTYStart = =Date(MakeDate(Year(addmonths(vMATTYEnd,-11)),Month(addmonths(vMATTYEnd,-11)),1))

The set Analysis

SUM({$<SH_TransactionDate={'>=$(vMATTYStart)=$(vMATTYEnd)'}>} [BILL FC Value])

What you achieve with this is that you can dynamically say from when the MAT should run (Max Date). So its not hard coded to the current month. I've created the couterparts to create the ability to compare the rolling 12 months TY against the same months LY. Its works lke a charm.

The Pivot Chart:

Dimension1: Whatever the main dim is. Cost center, Area, Town, Client etc.

Dimension2: Simply select the Month-Year dimension I've added the script -->(date(monthstart(TempDate), 'MMM-YYYY') as Sales_MonthYear) or whatever other date dimensions you're using as your dimension. MAKE SURE YOU DRAG YOUR DATE DIMENSION FROM ITS LOCATION (Left) TO THE TOP OF THE PIVOT TO SPAN OUT THE MONTHS like the attached item B it makes it easier to read when you start adding more dimensions.

Measurement: SUM({$<SH_TransactionDate={'>=$(vMATTYStart)=$(vMATTYEnd)'}>} [BILL FC Value])

Let me know if it helps / works.

Regard,

Nico

Attachment A.

Attachment B:

[#5]

I'm still not there on the formula:

Sum (
      {$<
           [Bill Date]=
                {"<$(
                     =addmonths(MonthStart(Today()),-1,0))
                     "}
           >}
[Bill FC Value])

I also thought maybe it was a date format issue so I tried

Sum (

         {$<

              [Bill Date]=

                   {"<$(

                       =date(addmonths(MonthStart(Today()),-1,0),

                                    'MM/DD/YYYY')

                  "}

             >}

        [Bill FC Value])

Finally, I'm not looking to calculate a moving average but sum of sales each month.  I'm not sure if that makes a difference in the formula or not.

I appreciate everyone's help

[JonnyPoole]

Craig - is your [Bill Date] wrapped in date() function during your load. This will ensure equivalency for the set analysis. addmonths() returns date() format you should be fine there without an extra date() within the set modifier search mask.

load

    [Bill Date]

->

load

    date([Bill Date]) as [Bill Date]

I would also suggest (if you make the above change to no avail) that you try the syntax i posted. It includes an preceding '=' sign to the search mask    {"=< etc...

in this situation its not equal to or less than its... the search mask is equal to:   <$(=<expression>) etc...

[tamilarasu]

Hi Craig,

Try like below,

=Sum({$<[Bill Date]={"<$(=Date(Date#(addmonths(MonthStart(Today()),-1,0),'MM-DD-YYYY'),'DD/MM/YYYY'))"}>}[Bill FC Value])

I would suggest you to create a variable like

Name:        vPrevMonth

Definition: =Date(Date#(addmonths(MonthStart(Today()),-1,0),'MM-DD-YYYY'),'DD/MM/YYYY')

Then, use expression as

= Sum({$<[Bill Date]={"<$(vPrevMonth)"}>}[Bill FC Value])

[Kushal_Chawda]

For calculating rolling period best way to do it is to create the flags in script

see the below link

Rolling N months

[jagan]

Hi Craig,

Check this link hope it helps you.

Set Analysis for certain Point in Time

Regards,

jagan.

[#10]

I ended up with this formula for each month:

Sum({< [Bill Date]={">=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))"}>} [Bill FC Value] )

It works if I don't have a date selection (ex 2015 12) at the top.

However, this is going to be part of a dashboard in which my boss will select the year and month.  Is there a replacement for $ that says "Ignore the Selection at the top"?  I tried deleting the $, replacing with 1, and replacing with {1-$} but haven't any luck.

Thank you