# How to find the total sales amount for a selected period

### Question

• Hi ,

I have two month dropdown fields in my SSRS report.

I need to find the sales amount between the selected months both inclusive.

Say i select feb in first drop down , june in second dropdown. I need to write a mdx query to show the total sales amount between the two dates. I have tried using the filter but its not working.

Can we define a filter to get the sales amount only for the months greater than or equal to feb and less than or equal to june ?

Thanks,

Srihari Thakkelapati

Tuesday, August 16, 2011 12:06 PM

• A slicer would have no effect on YTD function, since by default it uses range starting from first sibling and ending with the given member (constrained by YEAR level).

So to use slicer you should be able to leverage syntax provided by Manoj.

Or you can work with PeriodsToDate:

http://msdn.microsoft.com/en-us/library/ms144925.aspx

http://dailyitsolutions.blogspot.com/
Tuesday, August 16, 2011 2:50 PM

### All replies

• srihari,

You can write a simple MDX for this:

Select {[Measures].[total sales amount]} on 0

FROM ( SELECT ( STRTOMEMBER(@FromMonth) : STRTOMEMBER(@FromMonth) ) ON COLUMNS

FROM  [cubename])

or

Sum( STRTOMEMBER(@FromMonth) : STRTOMEMBER(@FromMonth) , [Measures].[total sales amount] )

Regards

Manoj

Tuesday, August 16, 2011 12:17 PM
• Hi Manoj,

The given below is the query i am using. I need to show the YTD leavers from Jan to July. OHC as on april. CHc as on July. I need to show the leavers from April to July. If i put a slicer can there be an effect on YTD leavers ?

The given below is the query i am using:

WITH

MEMBER

[YTD LEAVERS] AS

SUM

(YTD([TIME PERIOD].[HIERARCHY].[MONTH].&[7]),[MEASURES].[LEAVERS])

MEMBER

[OHC] AS

SUM

(MTD([TIME PERIOD].[HIERARCHY].[MONTH].&[4]),[MEASURES].[OPENING HC])

MEMBER

[CHC] AS

SUM

(MTD([TIME PERIOD].[HIERARCHY].[MONTH].&[7]),[MEASURES].[CLOSING HC])

SELECT

{

[Measures].[OHC],

[Measures].[CHC],

[Measures].[Leavers],

[Leavers for Period]

,[YTD LEAVERS]}

ON COLUMNS,

NON

EMPTY

{

(

[Location].[DESCR].[DESCR],

[Time Period].[Month].&[1]

)

}

ON ROWS

FROM

[ISG_BIM_PPL_HR]

WHERE

[Employee Type].[Type Id].&[1]

Thanks,

Srihari

Tuesday, August 16, 2011 1:06 PM
• A slicer would have no effect on YTD function, since by default it uses range starting from first sibling and ending with the given member (constrained by YEAR level).

So to use slicer you should be able to leverage syntax provided by Manoj.

Or you can work with PeriodsToDate:

http://msdn.microsoft.com/en-us/library/ms144925.aspx

http://dailyitsolutions.blogspot.com/
Tuesday, August 16, 2011 2:50 PM