Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a couple of expressions to do comparisons between periods. However, I am not able to get the previous year, MTD to work.
Currently I am using the below expressions for:
For Current Year MTD: /*This returns for the current month to the current date - 2017-07-01 > 2017-07-24*/
Sum({<Year=,Month=,Date={">=$(=MonthStart(max(Date),0))<=$(=AddYears(max(Date),0))"} >} Float)
For Current Year YTD:
Sum({<Year=,Month=,Date={">=$(=YearStart(max(Date),0))<=$(=AddYears(max(Date),0))"} >} Float)
For Previous Year YTD:
Sum({<Year=,Month=,Date={">=$(=YearStart(max(Date),-1))<=$(=AddYears(max(Date),-1))"} >} Float)
For Previous Year MTD: /*This needs to return for the current month, last year, to the current date - 2016-07-01 > 2016-07-24*/
???
I would appreciate assistance on this one.
Thank you in advance.
May be this?
Sum({<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} Float)
May be this?
Sum({<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} Float)
Current Year MTD: Sum (
{$<[FINANACIAL YEAR]=
{">=$(=yearstart(today(),0,7))<$(=yearend(today(),0,2)),<=$(=yearstart(today(),7))<$(=yearend(today(),2))"}
,[Month]={"*"}>} MEASURE)
Previous Year MTD: Sum (
{$<[FINANACIAL YEAR]=
{">=$(=yearstart(today(),-1,7))<$(=yearend(today(),0,2)),<=$(=yearstart(today(),0,7))<$(=yearend(today(),2))"}
,[Month]={"*"}>} MEASURE)
Hello John,
Below is sample expression:
//Prev. Year MTD
Sum({<Year=, Month=, Date={">=$(=MonthStart(AddYears(Max(Date),-1)))<=$(=MonthEnd(AddYears(Max(Date),-1)))"}>}Float)
Regards!
Rahul Pawar
Hello Rahul.
Thank you for replying.
I marked your solution as Helpful because I may be able to use it elsewhere.
Your "Sum({<Year=, Month=, Date={">=$(=MonthStart(AddYears(Max(Date),-1)))<=$(=MonthEnd(AddYears(Max(Date),-1)))"}>}Float)" Monthend takes it to the end of the month whereas I want it to date.
Thank you.
Hello Anil.
Spot on. Thank you very much. This works perfectly.