8 Replies Latest reply on Feb 10, 2019 8:43 AM by Shinichiro Murakami

# Month on Month analysis and spend calculation

Dear All,

I have came up with this stated issue mentioned below.

I need to input a comparison of spend between two different dates. That means today spend need to be compared by last month end date spend.  Example shown below

01/01/2019 Spend - 1500

01/20/2019 spend - 2000

01/26/2019 Spend - 5000

All the above figures need to be compared and insert a difference from the spend of 12/31/2018

07/01/2018 Spend (Need to be compared with the spend of 06/30/2018)

05/20/2018 spend - (Need to be compared with the spend of 04/30/2018)

02/26/2018 Spend - (Need to be compared with the spend of 01/31/2018)

Thank you very much

• ###### 1. Re: Month on Month analysis and spend calculation

Yes. I do have the same question...

• ###### 2. Re: Month on Month analysis and spend calculation

[Spend] - (

IF DATETRUNC('month', [Date]) = DATETRUNC('month',DATEADD('month',-1, [Date]))

THEN [Spend]

ELSE 0

END

)

The IF statement says, if the date equals last month's date, then retrieve the Spend for that month.  Take last month's [Spend] and subtract it from the current month.

• ###### 3. Re: Month on Month analysis and spend calculation

Thanks for your reply I went through your post and applied the same to my worksheet. It drags out the same spend data without any comparison. Pls do solve this out. My work book has been attached herewith

Thanks,

Willium Fox

• ###### 4. Re: Month on Month analysis and spend calculation

My mistake. Switch out the TODAY() function for [Date].  I edited the calculated field above.

I'm not in the office yet to open the workbook, so let me know if change does not work.

• ###### 5. Re: Month on Month analysis and spend calculation

Thank you very much for your reply. But that is not working. I went through your Calculatio but it drags the same spend as the previous coli spend. The calculatio is not providing me the exact spend for the last day of the last month . can you please help on this? Thank you very much and expecting your extended support in this...

• ###### 7. Re: Month on Month analysis and spend calculation

Thank you very much for your support and it worked like a charm for me.  I got one request from you. Can you please explain me the  techniques / reasons / output behind these 3 calculations? Why do we choose % for Bucket, What is the scenario behind these calculations?

Bucket1

DATE(

IF  MONTH([Order Date]) %2 =1 THEN DATETRUNC('month',[Order Date]) ELSE

DATETRUNC('month', DATEADD('month',1,[Order Date])) END -1 )

Bucket2

DATE(

IF  MONTH([Order Date]) %2 =0 THEN DATETRUNC('month',[Order Date]) ELSE

DATETRUNC('month', DATEADD('month',1,[Order Date])) END -1 )

Value of end of the last month

IF MONTH([Order Date]) % 2 =1 then

{fixed [Bucket 1] :MIN( if [Order Date] = [Bucket 1] then [Fixed spend] END)}

ELSEIF MONTH([Order Date]) %2 =0 then

{fixed [Bucket 2] :MIN( if [Order Date] = [Bucket 2] then [Fixed spend] END)}

END

Thank you very much

• ###### 8. Re: Month on Month analysis and spend calculation

You need to have two buckets to judge recent rolling two month.

To do that, just use odd month and even month with modulo..

#### % (modulo)

The % operator returns the remainder of a division operation. For example, 9 % 2 returns 1 because 2 goes into 9 four times with a remainder of 1. Modulo can only operate on integers.

Rest of them, the formulas are telling stories, you can understand even if it takes time.

Thanks,

Shin