9 Replies Latest reply on Jan 16, 2018 2:22 AM by Ruchika R

# How to get last month under quarter

Hi All,

I have one date field and one measure. I want to show last month of data under quarter like below:

Q1          Q2         Q3          Q4

march    Jun        Sep         Dec

• ###### 2. Re: How to get last month under quarter

Hi Tulu,

1. Create a MonthFilter = IIF(MONTH([Order Date])=3 OR MONTH([Order Date])=6 OR MONTH([Order Date])=9 OR MONTH([Order Date])=12,True,False)

2. Set MonthFilter to True

2. Create MonthName

3. For deriving Quarter use : DATEPART('quarter', [Order Date])

4. Set the objects as shown -

Hope this helps.

Regards,

Ruchika

• ###### 3. Re: How to get last month under quarter

Hi Tulu,

Please find attached the corresponding solution for your problem below. Please note that this is just an example. You can easily fit it to your needs.

IF MONTH(DATETRUNC('quarter', [Order Date]))=01 THEN 'Mar'

ELSEIF MONTH(DATETRUNC('quarter', [Order Date]))=04 THEN 'Jun'

ELSEIF MONTH(DATETRUNC('quarter', [Order Date]))=07 THEN 'Sep'

ELSEIF MONTH(DATETRUNC('quarter', [Order Date]))=10 THEN 'Dec'

END

Trust this helps.

Kind regards,

David

1 of 1 people found this helpful
• ###### 4. Re: How to get last month under quarter

Hi, Tulu

See my solution below

Hope this could help

ZZ

• ###### 5. Re: How to get last month under quarter

Hi,

This calculation giving the correct month but while applying any measure field then it is not giving the exact month value, I mean to say for 1st quarter it should give only march value but it is giving whole quarter(jan+feb+mar) value.

Kindly help me to get only march value for 1st quarter.

• ###### 6. Re: How to get last month under quarter

Hi David,

Your calculation should be dynamic , I mean to say, if December month is not present in table then last quarter should give November....like that....

• ###### 7. Re: How to get last month under quarter

Hi Ruchika,

Your calculation looking hardcoded/static to me. Kindly make any dynamic calculation like if last quarter does not contain Dec the it should show Nov .

• ###### 8. Re: How to get last month under quarter

Hi Tushar,

I have applied your calculation in filter shelf but I do not have Dec data(till Nov) so it should show Nov under quarter 4. Kindly do that and send it to me. Thanks

• ###### 9. Re: How to get last month under quarter

if month({ FIXED [Calculation2]:MAX(DATETRUNC('month',[Order Date]))})=MONTH([Order Date]) then [Sales] end

• ###### 10. Re: How to get last month under quarter

Hi Tulu,

I have modified the code to handle months dynamically.

Data Set -

Final Result -

Create Calculated field to track the max month -

Regards,

Ruchika