Display Sale by Month in Straight table

[#1]

Hi all,

I have a calendar like below.

using this date range I would like to create Month wise sale in STRAIGHT table

I have Variable called

vFromDate & vToDate

and Field name called DECDDT1 and [Net Sales]

I can mange to find Jan & March Sales using below expression.

sum({<DECDDT1 = {'>=$(vFromDate) <=$(=Monthend(vFromDate))'}>}[Net Sales])  //---- For Jan Sale

sum({<DECDDT1 = {'>=$(=monthstart(vToDate)) <=$(vToDate)'}>}[Net Sales])  ///--For March Sales

How do I get FEB sale? or if I Increase/decrease Date range?

Thanks

Vinay

[its_anandrjs]

Try like this

sum({<DECDDT1 = {'>=$(vFromDate) <=$(vToDate)'}>}[Net Sales])  //---- For Jan Feb Mar Sale

Hope this helps

[#3]

Hi Anand,

I want all months individually.

I Don't want Total of all Months which your expression gives me.

Vinay

[its_anandrjs]

In dimension use Month Dimension field and in expression use

sum({<DECDDT1 = {'>=$(vFromDate) <=$(vToDate)'}>}[Net Sales])  //---- For Jan Feb Mar Sale

Or

if if you have not month field extract like  Month(DECDDT1)

Hope this helps

[#5]

Hi Anand,

Thanks for reply,

But I want something like below in straight table.

Branch	Jan 2014 Sales	Feb 2014 Sales	March 2014 Sales
18	19,045	19,110	21,742
20	11,175	12,141	13,103
21	21,323	21,117	22,603
35	18,736	21,251	23,167
47	13,241	12,304	12,421
59	16,412	15,767	17,740
60	12,670	12,574	14,094
61	17,592	15,485	18,394
89	17,443	18,467	18,675
90	10,632	11,005	11,448
112	11,786	12,858	14,353
114	17,093	16,462	17,852
117	11,304	11,119	12,900
159	7,494	7,215	7,719

[#6]

I am not sure why you would want to have this as a straight table as that would mean that you would have to hard code every month (compared to having a pivot table) with a YearMonth-dimension as a column header.

Your Start and End Months can then be calculated as:

Month 0:

$(vFromDate)

MonthEnd($(vFromDate)),0)

Month 1:

MonthStart($(vFromDate),1)

MonthEnd($(vFromDate)),1)

Month 2:

MonthStart($(vFromDate),2)

MonthEnd($(vFromDate)),2)

Month 3:

MonthStart($(vFromDate),2)

$(vToDate)

You should probably. Combine this with something like:

IF(MonthEnd($(vFromDate)),3)>$(vToDate),MonthEnd($(vFromDate)),2),0)

But to be honest this starts looking really ugly and hard coded.

I would definitely go for a PivotTable with the YearMonth-dimension on top.

Kind regards

Niklas

[maxgro]

you can use AddMonths on MonthStart / MonthEnd to express other months

Floor(Addmonths(MonthEnd($(vFromDate)),1))

Pay attention to MonthEnd, from help

MonthEnd(date [, shift = 0])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date.

But as other people already suggested you're making a pivot with a lot of effort.......