ssasmdxdate-rangemdx-query

How to get measure value for certain date range in MDX?

I am new to MDX and for now it looks like some hell to me.

I have a measure called Sales KG. It calculate sales amount based on table AData where I have column named Data_Date.

I need to get Sales KG value for specified range of dates.

The problem is I can't understand how to specify that range. It doesn't look like simple < and > are works here.

I totally lost and don't have much to show, but this is what I tried:

select
[Sales KG] on Columns
from [Model]
where ([Format_TT].[Супермаркет], [Data_Date].&[20160101] : [Data_Date].&[20170101])

But it tells me that can't convert string "20160101" into date type. And probably this is not what I want. I want it to be single value for date range in single cell.

What to do?..

Solution

  • Take a look at the below sample they will help .

    Please note that "Date" is a dimesnion in the cube, which has an attribute "[Date]" in it. "[Measures].[Internet Sales Amount]" in the cube. It is necessary to have them defined in the SSAS project, If one of them is not defined in the project but exists in the base tables of star schema it will not work. MDX can only see objects defined in the SSAS project

    //First way

    select 
[Measures].[Internet Sales Amount]
on columns
from [Adventure Works]
where {[Date].[Date].&[20130101]:[Date].[Date].&[20130131]}

    enter image description here

    Second way 

    select 
[Measures].[Internet Sales Amount]
on columns,
{[Date].[Date].&[20130101]:[Date].[Date].&[20130131]}
on rows 
from [Adventure Works]

    enter image description here