Prior Year MTD and Prior Year QTD

[kishoreravi1983]

Hi All,

Can Some one help me with below expression how to derive Prior MTD and Prior QTD.

FDM= First Day of Month

below expression is giving me Prior YTD.

if(FDM >= AddYears(YearStart(today()),-1) and FDM <= AddYears(addmonths(MonthEnd(today()),-1),-1),month(FDM)&' '&year(FDM)) as [Prior YTD],

any Pointers are helpful.

Regards,

Ravi

[awhitfield]

Hi Ravi,

try out the following link:

YTQ, QTD, MTD and WTD

Andy

[rahulpawarb]

Hello Ravi,

Please refer below link:

Date Level Analysis - WTD, MTD, QTD &amp; YTD (Current Year &amp; Previous Year)

Hope this will be helpful.

Regards!

Rahul

[sunny_talwar]

May be flag like these... not sure what is the purpose of month(FDM)&' '&year(FDM):

If(FDM >= MonthStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior MTD Flag],

If(FDM >= QuarterStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior YTD Flag],

If(FDM >= YearStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior QTD Flag]

[kishoreravi1983]

Thanks Sunny for your reply.

month(FDM)&' '&year(FDM) this is to display the Value in Given format.

[kishoreravi1983]

Hi Sunny, I am not getting the Right Result.  Prior QTD FLAG should give me 2016, Q1 and Q2

Prior MTD should give me 2016 April or May, which ever is my current month in 2017

Can you plz relook if possible.

Thanks,

Ravi

[kishoreravi1983]

Hi Andy,

This Was nice Post in term of Writing has an Expression.

But i would like to Derive it at Script level. I am not giving the user any selection.

Regards,

Ravi

[sunny_talwar]

Got it... try this

If(FDM >= MonthStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior MTD Flag],

If(FDM >= QuarterStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior QTD Flag],

If(FDM >= YearStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior YTD Flag]

[kishoreravi1983]

Thanks Sunny..It was very Helpful.

Have a great day!

[kishoreravi1983]

Hi Sunny,

Hope you are doing good.

I am stuck with an Issue with Prior QTD.

Actually as per financial Year i am in Q3. First month

If(FirstDayofMonth >= QuarterStart(AddYears(Today(), -1)) and FirstDayofMonth <=Quarterend(AddYears(Today(), -1)), 1, 0) as [Prior QTD Flag],

But when am using this Prior QTD Flag it is still calculating for 2016-Q2 where has its should show me 2016-Q3 1 Month Data.

How do i write an Expression or short out this situation.

Please help me out. If you are free we can connect over the web or Go to meeting link for small call.

Regards,

Ravi