This Year MTD v Last Year MTD

[johngouws]

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.

[Anil_Babu_Samineni · Accepted solution]

May be this?

Sum({<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} Float)

[vicobi123]

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)

[rahulpawarb]

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

[qv_testing]

Previous YTQ, QTD, MTD and WTD

[johngouws]

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.

[johngouws]

Hello Anil.

Spot on. Thank you very much. This works perfectly.