How to extract last month's value for every Quarter

[gayatri7]

Hi All,

I have to convert the below chart based on different quarters. Which is easy and I did it (please ref 2nd chart). As expected, I am getting value for Q1 as Jan+Feb+Mar ; Q2 as Apr+May+Jun etc.

But Goal here is to get the last month's value for every quarter. Eg. if it is Q1 , I should get value as 10,216 (March)and not 30,441 (Jan+Feb+Mar) similarly for Q2 -10,546 (Jun) and not 31,444 (Apr+May+June)

Monthly

Quarterly

I tried something with Set expression but I am getting only one Quarter at a time (which is not as expected). I should get all quarters.

Could anyone let me know how to extract the last month's value of every quarter.

Thanks

[Mauritz_SA · Accepted solution]

Hi @gayatri7 

Maybe you can adapt this to work for you. I assumed that you don't use autoCalendar, otherwise you can obviously just use the Quarter field for your dimension .

LOAD RecNo() AS InvId, Date#(InvDate,'DD/MM/YYYY') AS InvDate, Sales Inline [
InvDate, Sales
28/03/2012, 20
10/12/2012, 25
5/2/2013, 32
31/3/2013, 15
19/5/2013, 19
15/9/2013, 20
11/12/2013, 25
2/3/2014, 10
14/5/2014, 5
13/6/2014, 2
7/7/2014, 10
4/8/2014, 13
];

Dimension:

Dual('Q'&Num(Ceil(Num(Month(InvDate))/3)),Num(Ceil(NUM(Month(InvDate))/3),00))

Measure:

Sum({<InvId = {'=Month(InvDate)=Month(QuarterEnd(InvDate))'}>}Sales)

OR

Sum(If(Month(InvDate)=Month(QuarterEnd(InvDate)),Sales,0))

Hope this helps.

Regards,

Mauritz