Last month of previous quarter?

[#1]

Hey guys,

I'm trying to select data for the month of the previous quarter and am having some trouble doing so. The months are like so.

Q1 - Jul, Aug, Sept

Q2 - Oct, Nov, Dec

Q3 - Jan, Feb, Mar

Q4 - Apr, May, Jun

And my MonthYear is as so:

Jan-14

Feb-14

Apr-14

etc. up to Jan-15

And my load statement for dates is as so:

    SET vFiscalOffset =6;

    Date(Date) as "Date",

    Floor(Date) as DateNum,

    Week(Date) as "Week",

    Month(Date) as "Month",

    'Q' & Ceil(Month (Date)/3) as Quarter,

    Year(Date) as "Year",

    Date(MonthStart(Date), 'MMM-YY') as MonthYear,

    num(year(addmonths(Date, $(vFiscalOffset)))) AS FiscalYear,

    'Q' & ceil(month(monthstart(Date, $(vFiscalOffset))) / 3) AS FiscalQtr,

    num(year(addmonths(Date, $(vFiscalOffset)))) & ' ' & 'Q' & ceil(month(monthstart(Date, $(vFiscalOffset))) / 3) AS FiscalYearQtr,

If a MonthYear such as Nov-14 is selected, I want to only have data showing for the last month of the previous Qtr, which would be Sept-14.

Currently I can do this for the previous month easily enough with this:

Sum({<MonthYear={"$(=Date(MonthEnd(Max(MonthYear),-1),'MMM-YY'))"}>}Price)

I've tried using the methods shown here:YTQ, QTD, MTD and WTD and the one for previous, but I still can't seem to get it to work.

If anyone is able to help that would be greatly appreciated.

(I'm not able to post the app sorry)

[jagan · Accepted solution]

Hi,

Try like this

Sum({<MonthYear={"$(=Date(MonthStart(QuarterEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)

Hope this helps you.

Regards,
Jagan.

[#3]

That's working like a charm thanks so much! I'm still learning when it comes to this type of syntax so it's great you guys are so helpful!

I haven't answered your answer as correct yet because I stil have a couple of things similar I'm trying to solve.

What would the equation be to select the end of the previous FiscalYear, and the end of the previous Year (not fiscal)?

End of previous FiscalYear being Jun-14 and end of previous year being Dec-14 in this case.

I can't seem to work it out.

[jagan]

Hi,

Try like this

For Previous Year (Not Fiscal) :

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)

Regards,

Jagan.

[jagan]

For

End of previous FiscalYear:

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1, 7)),'MMM-YY'))"}>}Price)

Regards,

Jagan.

[#6]

That's awesome man it's all working perfectly thanks so much! I actually worked out the year one just before you posted it, but had no clue about the fiscal year. Thanks for that!