Current month previous year expression

[Anonymous]

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

[shraddha_g]

Why do you want to combine it..

You can simply get it with Date.

[Anonymous]

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])

[Anonymous]

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:

({<MonthYear = {">=$(=Date(min(addmonths(MonthYear,-12)),'MMM-YYYY'))<=$(=Date(max(addmonths(MonthYear,-12)),'MMM-YYYY'))"}>}

Regards,

Sílvia

[Anonymous]

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

[shraddha_g · Accepted solution]

Try this for previous year current month

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

[Anonymous]

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?

[shraddha_g]

Yes correct..

[jacek_stypulkow]

Shraddha,

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

[Anonymous]

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