Periods of dates in pivot table

[#1]

Hi guys!

I have a table with a number of items came in or out in each stock for each day.

For example:

Date	Stock	Item	inORout
03.06.16	Stock1	Item1	3
04.06.16	Stock1	Item1	-2
02.07.16	Stock1	Item2	2
10.07.16	Stock1	Item2	-1
14.07.16	Stock1	Item1	-1

In Pivot table in the end should be:

Year-Month	Item	Balance
06-2016	Item1	1
07-2016	Item2	1
	Item1	0

There also can be Quarter-Year or Week-Year instead of Year-Month.

Please help to solve it. Thank you very much!

[kaushiknsolanki · Accepted solution]

All you need to do is create a flag in Script to identify the Last day. Something like this.

Load *,If(Date = Monthend(Date),1,0) as Monthend_Flag From Xyz;

Now go to pivot and put expression as

Sum({<Monthend_Flag = {"1"}>}Stock)

Regards,

Kaushik Solanki

[#3]

It is great! Very easy at the same time))

Thank you very much.

P.S. Little addittional informatio. To work for 100% we need to Floor() the MonthEnd(Date).