9 Replies Latest reply: Feb 24, 2017 2:47 AM by Mike Slottje

Current month previous year expression

Hi guys,

I want to compare the sales of the current month in the current year with the sales in the current month in the previous year.

I tried the following expressions:

sum({< Year={"\$(=max({<year>}Year)-1)"}, Date ={">=\$(=MonthStart(Today())) <=\$(=MonthEnd(Today()))"} >}[Nett Amount new EUR])

sum({< Year={"\$(=max({<year>}Year)-1)"}, Date ={">=\$(=MonthStart(Today()-12)) <=\$(=MonthEnd(Today()-12))"} >}[Nett Amount new EUR])

The Year (current year) and Date (current month) are working seperately, but not when combined.

It would be great if you guys could help me out!

Cheers:)

Mike

• Re: Current month previous year expression

Why do you want to combine it..

You can simply get it with Date.

• Re: Current month previous year expression

Because when I use only the Date expression, I get the sales of the current year instead of the previous year..

Any idea why that happens and how to solve it?

Note:

This is the Date expression I use

sum({< Date ={">=\$(=MonthStart(Today()-12)) <=\$(=MonthEnd(Today()-12))"} >}[Nett Amount new EUR])

• Re: Current month previous year expression

Hi Mike,

Do you have MonthYear in your Calendar Table?

If so you could use this set analysis for Current Month of current year:

{<MonthYear = {">=\$(=Date(Max(MonthYear), 'MMM-YYYY'))<=\$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>}

and this for current month of previous year:

Regards,

Sílvia

• Re: Current month previous year expression

Hi Sivia,

thanks a lot for your reply and your effort! I do have the MonthYear in my Calendar Table.

Unfortunately, the current year shows a revenue of 0 and the previous year shows an amount which is waaaaaay too big..

The latest expression I've tried is:

SUM({1<Date = {'>=\$(=AddYears( MonthStart (Today()),-1))<=\$(=AddYears( MonthEnd (Today()),-1))'}>}[Nett Amount new EUR])

Strange enough I get some similar revenues and some slighlty different revenues compared to the simple expression below.

Sum ({\$<Year = {'2016'},Month={'feb'}>}[Nett Amount new EUR])

So I'm still trying to find a proper expression haha

Regards,

Mike

• Re: Current month previous year expression

Try this for previous year current month

sum({< Date ={">=\$(=date(addmonths(MonthStart(Today()),-12))) <=\$(=date(addmonths(MonthEnd(Today()),-12)))"} >}[Nett Amount new EUR])

• Re: Current month previous year expression

Yeah that works perfectly! Thanks a lot Shraddha

For my understanding.. The main change is in the (date(addmonths(...))-12) you've added isn't it?

• Re: Current month previous year expression

Yes correct..

• Re: Current month previous year expression

Have similar question...

Have you suggestion how to modify below expression to get full previous month of current year?

sum({< Date ={">=\$(=date(addmonths(MonthStart(Today()),-12))) <=\$(=date(addmonths(MonthEnd(Today()),-12)))"} >}[Nett Amount new EUR])

Regards

Jacek

• Re: Current month previous year expression

Hi Jacek,

to get the full previous month of current year, I think you just have to change the '-12' into '-1'.

So the expression will look like this:

sum({< Date ={">=\$(=date(addmonths(MonthStart(Today()),-1))) <=\$(=date(addmonths(MonthEnd(Today()),-1)))"} >}[Nett Amount new EUR])

For the previous month in the previous year use -13.

Hope that helps!

Mike