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

Question

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?..

Do you have a dimension Data_Date in your cube, based on your question you are saying that Data_date is a column in your fact. It will not help if it is not a dimesnion in your Cube. MDX will only be able you access your cube's dimension and measures not the underlying star schema. You need to check what is the datedimension in your cube. — MoazRub

MoazRub MoazRub · Accepted Answer · 2019-02-13T15:39:57

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]}

Second way

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

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

1 Answers