Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this for previous year current month
sum({< Date ={">=$(=date(addmonths(MonthStart(Today()),-12))) <=$(=date(addmonths(MonthEnd(Today()),-12)))"} >}[Nett Amount new EUR])
Why do you want to combine it..
You can simply get it with Date.
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])
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
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
Try this for previous year current month
sum({< Date ={">=$(=date(addmonths(MonthStart(Today()),-12))) <=$(=date(addmonths(MonthEnd(Today()),-12)))"} >}[Nett Amount new EUR])
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?
Yes correct..
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
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