MTD Flag help

[satishkurra]

Hi

I'm trying to derive MTD Flag for Current Year and also for Last Year.

MTD Flag is from Current Year Month Start Date to Last Sunday

PMTD Flag is Previous Year Month Start Date to Last Sunday in Previous Year

Ex:

MTD Flag : 6/1/2017 - 6/25/2017

PMTD Flag: 6/1/2016 - 6/26/2016

MTD Flag is working but PMTD Flag is ranging from 6/1/2016 - 6/19/2016

I'm using below logic

If(MonthName(Today())=MonthName(Date) and Date >= MonthStart(Today(),0) and Date <= WeekEnd(Today(),-1,0), 1, 0) as MTDFlag,

if(Date >= MonthStart(AddYears(Today(),-1),0) and Date <= WeekEnd(AddYears(Today(),-1),-1,0), 1, 0) as PMTDFlag,

Request to please help

Thanks

Satish

[sunny_talwar · Accepted solution]

May be this

if(Date >= MonthStart(AddYears(Today(),-1),0) and Date <= WeekEnd(AddYears(Today(),-1),0,0), 1, 0) as PMTDFlag,

[satishkurra]

Thanks Sunny for your response

Will it work every week going forward

The reason behind the ask is i'm performing this check on Monday which is why i'm setting -1 to get Last day of WeekEnd which is Sunday every time

[satishkurra]

Also i do have problem with Previous Year Week Start Date

WeekStart(AddYears(Today(),-1),0,0)

[sunny_talwar]

So next week the  <= part will be 07/03/2017... if that is correct, then you have nothing to worry about

[sunny_talwar]

Not sure I understand?