4 Replies Latest reply on Jul 13, 2018 10:25 AM by Deepak Rai

# Sum If month = XXX and changes when new information is added

I am having a bit of a time with a calculation. I have months beginning with January 2016 to present. Every month when I refresh the data I will get the next month. For example, right now I have June 2018. In August when I refresh I will get July 2018.

I want to show my max date number of records and at the same time the prior 2 years so it would be like

June 2016                    June 2017                   June 2018

13000                            15000                          17000

When I refresh next month based on the calculation it will change to

July 2016                      July 2017                    July 2018

Whatever the sum of the number of records are for those months.

• ###### 1. Re: Sum If month = XXX and changes when new information is added

Hi Tina!

You could look to do something like this:

MONTH([Relevant Date]) = MONTH({MAX([Relevant Date])})

If you want to ensure you're only bring in the last two years worth as well, you can also add:

YEAR([Relevant Date]) >= YEAR({MAX([Relevant Date])})-2

This is dependent on you only loading data up through the last completed month. Otherwise you need to modify it to:

Hopefully this helps!

Best,

Bryce

• ###### 2. Re: Sum If month = XXX and changes when new information is added

Hi Tina,

Here it is

My Maximum Month Data was Dec 2016 , so you will see Results for November months. This Calc will always Remain Dynamic, you can make Names of your Measures some generic names to Refelect  1 year or 2 year back

Here are the Calcs

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to close Thread

• ###### 3. Re: Sum If month = XXX and changes when new information is added

Thanks so much. I played around and have a calculation like this set to 1 or 0.

if DATEDIFF('month',[Date],[Max Date]) = 12 or

DATEDIFF('month',[Date],[Max Date]) = 24 or

DATEDIFF('month',[Date],[Max Date]) < 1

then 1 else 0 END

I put that in as my filter and get June 16, June 17, June 18 and then just move my number records to the color for a bar chart and text to show the values.

1 of 1 people found this helpful
• ###### 4. Re: Sum If month = XXX and changes when new information is added

Innovative