Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello Ravi,
Please refer below link:
Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)
Hope this will be helpful.
Regards!
Rahul
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]
Thanks Sunny for your reply.
month(FDM)&' '&year(FDM) this is to display the Value in Given format.
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
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
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]
Thanks Sunny..It was very Helpful.
Have a great day!
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