It’s common in most cubes to have a number of different date dimensions, whether role playing, distinct, or a combination of both. Say for example, Entry Date, Posting Date and Accounting Period. There may also be numerous hierarchies in each date dimension, such as calendar and fiscal calendar, leading to a relatively complicated array of dates to worry about when calculating semi-additive measures.
If we create a date related calculation (i.e. total to date) how do we ensure that this calculation works across all date dimensions?
Lets assume we have a stock movement measure, where each record in the fact table is the change in stock (plus or minus). The current stock level is found by using a calculation totaling every record to date.
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
SUM({NULL:[Date].[Calendar].CurrentMember}
, [Measures].[Stock Movement]
);
[Note that {NULL:xxx} just creates a set of everything before the xxx member, i.e. everything to date]
This works just fine, if the user selects the [Date].[Calendar] hierarchy. What if the user selects the [Date].[Fiscal] hierarchy, or the [Period] dimension? Basically the calculation wont work, as the MDX expression is only aware of the [Date].[Calendar] hierarchy.
The simple solution is to use the Aggregate function over all of the dimensions that the calculation needs to be aware of:
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
AGGREGATE(
{NULL:[Date].[Fiscal].CurrentMember}
* {NULL:[Date].[Calendar].CurrentMember}
* {NULL:[Period].[Period].CurrentMember}
, [Measures].[Stock Movement]
);
The calculation will then use whichever date or time hierarchy is selected. It will even cope if multiple dimensions are selected, say the calendar on 0 and the periods on 1, both axis will honor the aggregation as expected.
Frog-Blog out.
I like this approach but really need to tweak it a bit. The need is to sum a balance sheet item like Mortgage Amount which is stored in the fact table as an amount per accounting period. I need to sum from beginning of time to the member from the level selected in one hierachy. Something like this:
Calculate;
CREATE MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy]
AS AGGREGATE(PeriodsToDate(
{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Year TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
*{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Quarter TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
*{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Account Period TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
, [Measures].[UDAG Number])
),
FORMAT_STRING = “$#,##0.00;($#,##0.00)”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;
Any suggestions?
Hi Chris
It looks like you’re overcomplicating this a little – all three attributes that you want to aggregate by (Year, Quarter and Period) are in the same [Fiscal TA Hierarchy], so you don’t need to worry about aggregating across multiple hierarchies. Instead, you can just sum from NULL to the current member of the hierarchy, which will automatically take care of the selected level (year, qtr, period).
Try something like this
CREATE
MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy] AS
Aggregate
(
{NULL : [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
,[Measures].[UDAG Number]
)
,FORMAT_STRING = “$#,##0.00;($#,##0.00)”
,VISIBLE = 1
,ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;
Let me know if this doesn’t do what you want.
Alex
Hi Alex,
I’ve wrote something like this to get the amount from starting to the current selected date.
CREATE
MEMBER CURRENTCUBE.[Measures].[AmountCur] AS
Aggregate
(
{NULL : [[Time].[Years Quarters Months Weeks Days]CurrentMember}
,[Measures].[AmountCur])
,FORMAT_STRING = “$#,##0.00;($#,##0.00)”
,VISIBLE = 1
;
But the above is not worked for me. Can you help me in this.
Thanks,
Rakesh
Excellent – works a treat, I was thinking you’d need a calculation for each hierarchy in the time dimension !
How would one handle this using a DateTool for comparison/aggregation calculations across multiple time dimensions?
SCOPE([DateTool].[Aggregation].[Year To Date]);
THIS =
AGGREGATE(
YTD(
NULL:[Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember *
NULL:[Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember
);
END SCOPE;
Produces a #VALUE! as the YTD function expects a member expression, and the NULL causes it to be considered a tuple.
Thanks!
Hi Nick
If you’re using YTD you wouldn’t need to use {NULL:xxx} as {NULL:xxx} returns a set of history to date. YTD takes a single member and returns you a set of year to date.
You should be able to use
AGGREGATE(
YTD([Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember) *
YTD([Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember)
Hope this helps – let me know if you get any problems
Alex
Alex-
When I use the YTD method you described, I seem to only be able to see values if I have both dimensions present in the query (either row or column). What I’d like to do is have it work even if I’m only looking at one dimension.
I have an Activity Date and an Invoice Date. If someone is looking at just invoice date, it should just work. Instead I get Nulls. Any ideas?
James
Hi James
There should be no need to select members in both dimensions, so long as they both default to the [All] member. If they don’t then the code wont work and you’ll need to manually select a member in both.
Hope this helps
Alex
Alex
I’ve pillaged your code and have got 90% of it to work perfectly, thank you!
I’m using a utility dimension and trying to set up a YTD calc that spans multiple date hierarchies. I can get it to work fine with one date dimension, but as soon as I add a second date dimension the results come up as ‘NULL’ unless I select a member from both date dimensions.
Here’s my code:
AGGREGATE(
YTD([Date1].[Date].CurrentMember) * YTD([Date2].[Date].CurrentMember),
[Measures].[Count])
I’ve also tried with ‘PeriodToDate’ and it does the same thing, also when I run this directly as MDX against the cube.
The base date dimension is set up to default to ‘all’. I have another calculation on the utility dimension that does a simple cumulative calc, using AGGREGATE and {NULL:[Date].[Date].CURRENTMEMBER}, and this works fine across all our date dimensions.
If you have a bright idea I’d be very grateful!
Many thanks
Tim
Hi Tim
The problem that you’ve got is that YTD doesn’t make sense with the All member. YTD and PeriodToDate need a member selected within the year or period. i.e. with YTD(All), which year would it select?
You should find the last descendant of the current member, and then pass that into YTD. Something like this:
YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember,,LEAVES),1).item(0))
Then, if All is the current member, the last member will be selected and YTD will have something to go on.
Hope this helps,
Alex
I want to check that if a given month lies in a span of dates. For example- I want to check if June, 2017 lies between 01-01-2017 and 01-12-2017. Can you help me with MDX
Hi Anand
You could use EXISTS to determine whether any dates in the first set (June 2017) exist in the second set (Jan-Dec). Then just take a count of the resulting set. If it’s 0 then it is not within the second date span, if it is >0 then there is crossover.
Something like:
EXISTS(DESCENDANTS([Date].[Calendar].[Month].[2017-07], [Date].[Calendar].[Date]), {[Date].[Calendar].[Date].&[20170101]:[Date].[Calendar].[Date].&[20171201]}).MEMBERS.COUNT
Thanks
Alex
i HAVE AN HR PROJECT, WANTS TO CREATE A REPORT ON GENDER DIVERSITY, CALCULATION NEEDE AS :
AVG OF MALE AND FEMALE BY FY1920.
Row Labels Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
Female 13 13 14 15 15 15 15 15 17 18 18 18
Male 34 3 5 35 34 35 33 32 32 34 35 34 34
Grand Total 47 48 49 49 50 48 47 47 51 53 52 52
I HAVE PLOTTED THIS AS HARD CODED IN EAZYBI USING CASE WHEN CONDITION WITH TIME.
NOW I JUST NEED TO CALCULATE IT’S AVERAGE TO PLOT IN PIE CHART, I AM STRUGGLING SINCE 3 DAYS BUT I DIDNT FIND THE WAY TO COMPLETE IT, PLS HELP IF ANYONE IS ACTIVE AND WATCHING THIS.
Hi Alex,
Your examples have been really helpful, I was wondering if you could help out a little more.
In this case I’m trying to do an aggreate based on the Previous 30days of the current member
I’m using a Date-Time Hierarchy, so the current member could be a year, quarter, month, day.. so I’d likely be looking at 30days from the lowest value in the current member.
Right now I have one aggretation that does:
Aggregate
(
{[End Date].[Date-Time Tree].CurrentMember:NULL }
,[Measures].[MembersCount]
)
— Though I’d really like to include a ‘[Begin Date] is less than current member as well to avoid counting records that haven’t ‘begun’ yet.
In the end, I’d like to be able to look at a time line and see at any point in time, how many records were ‘current’ (their end date) is on or after that point in time, -30 days past due, -60 days past due and -90 days past due… I figure once I get the 30 it’s just lather, rinse and repeat for any other values I want.
really hope you can help,
Tony
Hi Tony
The first stage would be to identify the leaf member of the hierarchy as the starting point, I.e find the date from whichever level is selected in the hierarchy.
DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)
You can then use lag to find the previous 30 members and build a set:
{DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0).lag(30)
:DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)}
Which you can then put into any calculated member.
Hope this helps
Alex
Alex,
Thank you so much! that just about did it. My numbers are slightly off, but I suspects that’s because I need to use a DistinctCount.
Thank you again for your help!
Tony
Hi Alex
Many thanks for your response! I think I’ve confused things… I meant to convey that I’ve not set up any user-defined defaults on my date dimensions, so I don’t think I’m falling foul of the point you raised re: James’ post above, about having custom default members.
I’m basically trying to follow exactly the approach you described earlier in the post:
AGGREGATE(
YTD([Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember) *
YTD([Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember)
I’m trying to get this working in principle in MDX, before I add it to the utility dimension. Accordingly, I’ve tested the following:
WITH
MEMBER Test1 AS
AGGREGATE(YTD([Date1].[Date].CurrentMember),[Measures].[Count])
MEMBER Test2 AS
AGGREGATE(YTD([Date1].[Date].CurrentMember)*YTD([Date2].[Date].CurrentMember),[Measures].[Count])
MEMBER Test3 AS
AGGREGATE(
YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember),,LEAVES),1).Item(0))
* YTD(TAIL(DESCENDANTS([Date2].[Date].CurrentMember),,LEAVES),1).Item(0))
,[Measures].[Count])
SELECT {[Test1],[Test2],[Test3]} ON 0, [Date1].[Date].[Month].MEMBERS ON 1 FROM [Cube]
Test1 works fine, Test2 and Test3 both return Null.
Forgive me if I’m missing something very obvious here – anything you can suggest would be really useful!
thanks
Tim
Hi Tim
Custom default members or not, you still need to pass a valid member into the YTD function. If you haven’t set a custom default, then the default will be All. You can’t do YTD(All), it just doesn’t make sense. You therefore need to ensure that a valid member is always presented to the YTD function, whether the user has selected a member of the date hierarchy or not.
The tail(descentants(… code should ensure that a valid member is always presented. Can you try it and see if it works?
Alex,
Thanks for this post. Using your approach I’m trying to accomplish the following:
I’ve the following calculated member to count returning clients (clients who ordered from beginning to previous period) :
CREATE MEMBER CURRENTCUBE.MEASURES.[Returning Clients] AS
DISTINCTCOUNT
(
NONEMPTY(
NONEMPTY([Client].[Client ID].[Client ID].members, [Measures].[Charge Amount])
, {[Measures].[Charge Amount]} * {
NULL : [Accession Date].[Year Qtr Month Date].CURRENTMEMBER.PREVMEMBER}
)
)
As you can see I’m using the [Accession Date].[Year Qtr Month Date] hierarchy.
I need the above calculation to work with another hierarchy([Accession Date].[Week End Date]) that I have.
Thanks,
AJ
Hi AJ
The technique described here only works when using the CURRENTMEMBER of the date hierarchies, unfortunately using PREVMEMBER complicates matters somewhat.
The problem you have is that, if the attribute relationships are set up correctly, selecting a week in the week hierarchy will also select the related year in the month hierarchy. Therefore PREVMEMBER applies to both hierarchies to find the cross join of the previous week AND the previous year.
The easiest way around this is to first of all create a calculation which uses CURRENTMEMBER of both hierarchies (you can hide this). Then another calculated member which calls this with the PREVMEMBER of the selected hierarchy. You can determine which hierarchy to use by looking at the LEVEL.ORDINAL property of the CURRENTMEMBER of each of the hierarchies.
Something like this…
CREATE MEMBER CURRENTCUBE.MEASURES.[_Returning Clients] AS
DISTINCTCOUNT((
[Client].[Client ID].[Client ID].members
, ([Measures].[Charge Amount]
,{NULL:[Accession Date].[Year Qtr Month Date].CURRENTMEMBER}
*{NULL:[Accession Date].[Week End Date].CURRENTMEMBER}
)
)
)
, VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.MEASURES.[Returning Clients] AS
IIF([Accession Date].[Year Qtr Month Date].CURRENTMEMBER.LEVEL.Ordinal
> [Accession Date].[Week End Date].CURRENTMEMBER.LEVEL.Ordinal,
([Measures].[_Returning Clients]
, [Accession Date].[Year Qtr Month Date].PREVMEMBER)
,([Measures].[_Returning Clients]
, [Accession Date].[Week End Date].PREVMEMBER)
)
)
Also note that you don’t need to use NONEMPTY. NONEMPTY is a display function, not a calculation function. the DISTINCTCOUNT will only count the non empty values so you don’t need to specify it. You only need to use NONEMPTY if you want to stop empty members from being displayed in the results.
Good luck with this, hope it helps
Alex
Alex,
I need to write a query that would do the following:
Return YTD, QTD, MTD values for an accounting year dimension based a selected reporting period dimension, also the query should return the closing period values and opening period values. How would go about constructing this query.
Thanks in advance for your help
Pierre
Hi Pierre
The code will depend on the relationship between the accounting date hierarchy and the reporting period hierarchy. In a simple example, assuming they are the same (e.g. a single date hierarchy of Date, Period, Qtr, Year), you’d use the PERIODTODATE function to calculate the values for you, something like this…
(please note that I’ve just written this freestyle, and not run or tested it, so don’t expect it to work straight off!)
WITH MEMBER [Measures].[Year To Date] AS
SUM(
PERIODSTODATE([Date].[Date Hierarchy].[Year],
[Date].[Date Hierarchy].CurrentMember)
, [Measures].[Value])
MEMBER [Measures].[Qtr To Date] AS
SUM(
PERIODSTODATE([Date].[Date Hierarchy].[Qtr],
[Date].[Date Hierarchy].CurrentMember)
, [Measures].[Value])
MEMBER [Measures].[Period To Date] AS
SUM(
PERIODSTODATE([Date].[Date Hierarchy].[Period],
[Date].[Date Hierarchy].CurrentMember)
, [Measures].[Value])
MEMBER [Measures].[Period Opening] AS
([Measures].[Period To Date]
,[Date].[Date Hierarchy].[Period].PREVMEMBER)
MEMBER [Measures].[Period Closing] AS
[Measures].[Period To Date]
SELECT {[Measures].[Year To Date]
, [Measures].[Qtr To Date]
, [Measures].[Period To Date]
, [Measures].[Period Opening]
, [Measures].[Period Closing]
} ON 0,
[Date].[Date Hierarchy].[Period].MEMBERS ON 1
FROM MyCube
Hope this helps
Alex
Hi Alex
I have one Calender year Hiararchy in my project and I want to write an MDX which will do the aggregations over a period of time like from 1/15/2000 till 4/15/2010. Since its an aggregation over a period of 10 years. I want to be able to write an MDX where it pulls data out on daily basis for the month of Jan,2000 since it starts on the 15 and goes till 30 and then monthly from Feb,2000 till 1/1/2001 and then yearly onwards. Since the start and end dates will be changing, how can I write an MDX where it decides by itself where to pull the data from. Like if the start date is 1/1/2010 till 1/1/2012. It will know that it has to pull two records at the yearly level. Would really appreciate your help.
Thanks
-Sarah
Hi Sarah
You don’t need to worry about whether to pull the day/month/year data out, the cube engine will take care of that for you. You just need to ask for the date range and the cube will automatically determine the optimal aggregations to use to satisfy the query.
MDX is easier that most people think in this regard – let SSAS do the hard work for you!
Alex
Nice approach, though it seems you would get a performance hit with the cross joining. Have you tested performance, comparing against other methods of producing similar results ?
That is a very good point which I should have pointed out. Yes you’ll get better performance using SCOPE and only referring to a single date hierarchy, however SCOPE is a more complex approach which stumps most MDX beginners, so this method provides a more simple alternative. I should also say that I haven’t run timing tests on this, however I would expect the performance hit to be minimal, as one of the date hierarchies will always be [All] (you’d only chose the month or week hierarchy, not both), therefore you’re not cross joining two large sets.
Alex
Hi Alex,
I am having Time Period Dimension which is having Program Year attribute which have members like 0-12 Months, 13-24 Months and 25-36 Months. Time Period is having Default Member 0-12 Months which is my requirement. So for Other Dimensions across all measures it shows 0-12 Months value. But two of my measures have requirement that Across all other dimension it should show All Time Period Value. How this is possible. Please suggest. Please note my two measures are distinct count measures.
Thanks.
Hi Alex,
I am having Time Period Dimension which is having Program Year attribute which have members like 0-12 Months, 13-24 Months and 25-36 Months. Time Period is having Default Member 0-12 Months which is my requirement. So for Other Dimensions across all measures it shows 0-12 Months value. But two of my measures have requirement that Across all other dimension it should show All Time Period Value. How this is possible. Please note my two measures are distinct count Measures. Please suggest
Thanks.
Hi Alex,
after some days struggling around with different hierarchies in my time dimension I found your post, this saved my day 🙂 closing period value solved, but…
is there a similar approach for opening balance calculation as well? If you could propose a solution based on your example it would be very helpful.
Thanks,
Gregor
Hi Alex,
I want to show one my dimensions All Level Member to be NULL. I am using scope to do so but as other dimenions will treat this all level member to show that measure. So it shows it null. So is it there any way for only showing purpose I make that all level member to be null but while browsing across all other dimension it will consider its actual all level value to be non null.
please suggest.
Thanks,
Abhijeet
Hi Alex,
I have tried all of your suggestions but to no avail!!!!
Can you please try to help Me!!!
I currently have 3 role playing date dimensions on my cube, StartDate, EndDate and ApprovalDate.
I am trying to make a calculated member, that obbeys to filter on the three dimensions, and returns a sum of the number of active projects.
I have been able to make the query work on SQL, but have no idea on how to implement that on MDX.
The SQL Query resambles this:
SELECT D.YearInt,D.MonthNumber,D.MonthNameChar
,COUNT(DISTINCT ProjectID) as NProjet
FROM [FactProjects] FP ,[DimDate] D
where
(FinishDateProject)<='20130301'
and (StartDateProject))<='20130301'
and(D.YearInt=2013 and D.MonthNumber<=03)
GROUP BY D.YearInt,D.MonthNumber,D.MonthNameChar
order by D.MonthNumber
This returns a query with this format:
Year MonthNumber MonthName NProject
2012 1 January 351
2012 2 Fevereiro 351
2012 3 Março 362
So these are the total of active projects on each month for the current Year and month Filtered.
So this means i will have a total for each month based on the start and finish date.
Please Suggest!
Thank You!
Ricardo
Hi Ricardo,
This is an interesting one, which I’ll answer in two stages.
1) This is a data modelling problem, not an MDX problem. The technically correct way of approaching this would be to transform your data in your ETL into a structure more conducive to this style of reporting. If you’re going to SQL Bits in the UK in May then I’ll be presenting a talk on exactly this, how to remodel your data to better suit cubes and MDX. The theory is that with correct data modelling the MDX should be simple.
2) Assuming that you don’t have the luxury of redesigning your warehouse or cube, we’ll have to tackle it in MDX. This is a different problem to the original focus of the post, but it can be tackled in a similar way, but with a few crucial changes.
You want to filter your projects to those with a start date before the selected date, and with an end date after the selected date. Lets take each part in turn.
All projects which start after a particular date (assuming your hierarchy in your date dimension is called ‘calendar’):
AGGREGATE({[Start Date].[Calendar].[20130301]:NULL}, [Measures].[Project Count])This creates a set of all start dates after that selected, and then sums the project count.
All projects which end before a particular date:
AGGREGATE({NULL:[End Date].[Calendar].[20130301]:NULL}, [Measures].[Project Count])Combine them together, to get all projects which start after, and end before the specified date:
AGGREGATE( {[Start Date].[Calendar].[20130301]:NULL} * {NULL:[End Date].[Calendar].[20130301]:NULL} , [Measures].[Project Count])This basically finds the intersection of the two sets, and returns the project count.
Then you have the problem that you don’t want the user to choose a start date and an end date. You want them to select a single value from the 3rd [Date] dimension, and use that to apply to the above calculation.
This is where we get to use an awesome function, LINKMEMBER. It links a member of one role playing dimension and maps it onto a different dimension.
AGGREGATE( {LINKMEMBER([Date].[Calendar].CURRENTMEMBER, [Start Date].[Calendar]):NULL} * {NULL:LINKMEMBER([Date].[Calendar].CURRENTMEMBER, [End Date].[Calendar]):NULL} , [Measures].[Project Count])This takes the current member of the Date dimension, maps it to the start and end date dimensions, uses those to find the relevant project count.
Alex
Hi Alex,
Thank you so much for your answer, it solved my problem.
I wish i coul attend SQL Bits in the UK but that won’t be possible.
Can you suggest me some reading on trying to find a better way to modelling the data on this exact problem??
Thanks
Ricardo Santos
Hi Ricardo,
Probably the best resource of generic data modelling learning is from Ralph Kimball himself, he does a number of data modelling courses. Any book by Ralph will be worth reading.
http://www.kimballgroup.com/data-warehouse-and-business-intelligence-courses/
For this exact problem I’d look at changing the fact table so that each project is stored twice, and only a single date dimension. One row would store the project start date, and +1 as a project count. The second row would store the end date, and -1 as the project count.
You can then just sum the project count from {NULL:[Date].[Calendar].CURRENTMEMBER} to get the number of projects active on the selected date. You can also sum the project count for a single day, month, etc. to get the net change in the number of projects over that period. You get quite a lot of reporting flexibility by modelling the data as events instead of items. i.e. think of a project not as a single item, but as two distinct events; start and end.
The right solution will depend on your reporting requirements, but this may be a good starting point, which would certainly simplify your MDX.
Good luck with it
Alex
Hi Ricardo
Hope you don’t mind, I’ve taken your question and used it as the basis of a new blog post
MDX Between Start Date and End Date
Alex
Hi Alex,
No problem whatsoever!!
Now that i was able to solve it with your help and doing more and more of this type of queries, i hope i can be of some assistance!
Thanks.
Ricardo
Hi Alex,
I’m struggling with two time-related dimensions: one is a hidden Date dimension with one hierarchy [Year > Quarter > Month] and the other is a Period dimension with one hierarchy [Custom aggregation period > Month] the content of which can be defined by the user (Excel import during ETL). The user can define custom aggregation periods (such as [Current year to date], [School year 2011-2012] etc.). The Period dimension is tied to the fact using a many-to-many relationship with an intermediate measure group period-date. The leaf level is month, but since a month can be used under multiple parents each month has its own key and (so multiple Jan-2013 members will have a different key in the Period dimension).
This setup has been working for quite some time, but now I’m am running into trouble with some calculations. I intended to use the hidden Date dimension for calculations (with unique months) but I’m not sure how to identify the proper month member for the closing period of a given Period member.
The closest I get to is the following for a year to date calculation (my example here is a simple sales cube for testing, but in reality the cube is for a social welfare organisation and my calculations involve various lead time calculations).
WITH MEMBER [Measures].[Cumulative Sales]
Sum(
(
Ytd(
Tail(
Filter(
[Date].[Date].[Month].Members,
Not IsEmpty([Measures].[Sales Count])
)
).Item(0)
),
[Period].[Period].DefaultMember
),
[Measures].[Amount]
)
SELECT
Descendants([Period].[Period].[2013 – M01 through M04]) ON 0,
[Customer].Members ON 1
FROM
[My Test Cube]
WHERE
[Measures].[Cumulative Sales]
This query does not return the proper results because it sums only correctly if the customer has sales in the closing period (M04 = April). Obviously this is something I impose explicitly by the Not IsEmpty([Measures].[Sales Count])
My question in short is: how do I get the real month member (from the Date dimension for a given pseudo-month member of the Period dimension)?
Your help would be greatly appreciated.
Kind regards,
Jan-Willem
Hi Alex,
I have found a solution myself. To get the real month member in the Date dimension for a given pseudo-month member of the Period dimension, I use the following expression:
NonEmpty(
[Date].[Date].[Month].Members *
ClosingPeriod(
[Period].[Period].[Month],
[Period].[Period].CurrentMember
),
[Measures].[Intermediate Measure Group Row Count]
).Item(0).Item(0)} * [Period].[Period].DefaultMember
Key is the use of the (typically hidden) measure from the intermediate measure group that is used for the many-to-many relationship. Furthermore, it is important to include the cross join with the [Period].[Period].DefaultMember. This ensures that the expression in which this construction is used, only depends on the Date dimension and not on the Period dimension. If, for example, the current member of Period is [School year 2012-2013] but the time intelligence should also cover months prior to August 2012 (= start of school year), the cross join will prevent the current member of the Period dimension from narrowing the scope of the calculation to the months August 2012 and later.
A drawback of the cross join is that the Period attribute of the Period dimension should be set to be aggregatable, otherwise the DefaultMember would point to a member of the dimension that does not necessarily cover all months. Given the nature of the Period dimension, aggregation of periods (e.g. [Schoolyear 2012-2013] and [2012 – Q3]) is not useful.
Do you have suggestions for improvement?
Kind regards,
Jan-Willem
Hi Jan-Willem
Thanks for the follow-up post with a solution, sorry I’ve not had any time to look into this. I don’t have any sugegstions I’m afraid; with something as intricate as what you’re trying to do the only real way I’d be able to offer any advice would be to set up a working cube which mirrors your setup and then run a number of tests. This is a little too time consuming, in depth and specific to cover off in a blog question, and would require some consultancy time.
Time for blogging is very limited at the moment I’m afraid! (as you’ll see from the low number of posts recently!!!)
Regards
Alex
Hi Alex,
I have been stuck on Calculated Member Spanning Multiple Date Dimensions for a while now, and I’m starting to lose it :).
Basically I have two role playing dimensions, selling and arrival (each have 1 different hierarchy)
I’m using a Date Comparison shell dimension with the attributes regular, previous year, YTD.
Below are a few examples of MDX I have tried. Any help would be greatly appreciated!!!!!
Previous year (this works fine for date selling dimension, but I’m having no luck getting including date arrival dimension)
SCOPE ([Date Selling].[Date].MEMBERS,[Date Comparison].[Comparison].[Previous Year]);
this =
(ParallelPeriod([Date Selling].[Calendar Year – Month – Date].[Calendar Year],1, [Date Selling].[Calendar Year – Month – Date].CurrentMember),
[Date Comparison].[Comparison].DefaultMember);
END SCOPE;
YTD (again, works fine for 1 dimension)
SCOPE (
[Date Arrival].[Arrival Year – Month – Date].MEMBERS,
[Date Arrival ].[Date].MEMBERS);
( [Date Comparison].[Comparison].[YTD] )
= Aggregate(
{ [Date Comparison].[Comparison].DefaultMember } *
PeriodsToDate(
[Date Arrival].[Arrival Year – Month – Date].[Arrival Year],
[Date Arrival].[Arrival Year – Month – Date].CurrentMember
) );
END SCOPE;
I have tried some examples based on your previous answers.. but this only returns #VALUE! e.g.
SCOPE([Date Comparison].[Comparison].[YTD]);
this =
AGGREGATE(
PeriodsToDate([Date Arrival].[Arrival Year – Month – Date].CurrentMember) *
PeriodsToDate([Date Selling].[Calendar Year – Month – Date].CurrentMember)
, [Date Comparison].[Comparison].DefaultMember);
END SCOPE;
Hi Brad
Could you send me a backup of the cube? Difficult to diagnose MDX scope problems without being able to run tests.
Thanks
Alex
Sure. How do I attach a file here?
Hi Alex and Brad,
I think i have been trough a problem like this and i think i can help.
Here is my small contribution.
I also have a Calculated member spanning across multiple date hierarchy.
I have used a method that although slower than other possible solutions, will work fine!
Let me explain my scenario.
I use a single date hierarchy to filter all my objects, but i have a calculated member that spans across four different date hierarchys.
This is the method i used to be able to achive the calculated member.
// Calculated member that returns projects that have already started, and have no
// finished on a selectd date range
CREATE MEMBER CURRENTCUBE.[Measures].[Running Projects]
AS AGGREGATE(
((NULL:LinkMember( [Date].[Calendar].currentmember, [Start Date].[Calendar])) //Started project after selected date
,(LinkMember( [Date].[Calendar].currentmember, [End Date].[Calendar]) : NULL)) //Project that end after selected date
,[Measures].[Measure]);
NOTE: i simplified the script to two single date hierarchys.
Hope this helps!!!
Hi Ricardo, thanks for the suggestion
LinkMember can solve a lot or problems, as can cross joining dimensions. I can’t test it to see what results it gives, but performance will always take a hit – always better to try and use scope where possible if performance is an issue.
Try something like this…
SCOPE([Date Comparison].[Comparison].[YTD Test]);
this = AGGREGATE(YTD([Ship Date].[Calendar].CurrentMember)
,[Date Comparison].[Comparison].DefaultMember);
SCOPE([Ship Date].[Calendar].[All]);
this = AGGREGATE(YTD([OrderDate].[Calendar].CurrentMember)
,[Date Comparison].[Comparison].DefaultMember);
END SCOPE;
END SCOPE;
It calculates on the ship date, however if the [all] member of the ship date is being used then it will switch to using the order date.
In the example cube you provided the Fiscal date hierarchy isn’t set up correctly so YTD wont work with it, so I’ve used the calendar hierarchy from both to make it work.
Hi Alex,
Thanks for you answer. It looks to be what I’m after, however I’m still having trouble using the different hierarchies. You mention the fiscal date hierarchy is not setup properly. So maybe the hierarchies are my problem.
Thanks
Couple of things to look at: YTD needs the attributes to be set up correctly, Fiscal Year is not set up as a year at the moment.
Also the attribute hierarchy isn’t set up properly from date to month to fiscal year.
I see what you mean about the fiscal year… thanks.
The solution you provided almost gives me what I need. However I need to have the possibilty to filter by order date and have ship date in rows and vice versa, filter by ship date and have order date in the rows. Using the example you provided, The YTD is only represented correctly with order date as filter and ship date in rows. With ship date as filter and order in rows, the regual value is shown as YTD
Anyway this is really useful so far! Thanks
Hi Brad,
If you have both hierarchies in the query then the cube has no way of knowing which one you want to filter by, so your best option is to have two separate measures.
Alex
Hi Alex,
I’m struggling with a problem which relates a bit to this subject. I’ll give it a try posting it. Hopefully I’ll get an answer which helps me further.
In SSAS I am looking for a way to calculate a previous year total on (amongst others) the measure gewicht (weight). I have used the calculation script which I’ve copied below. I have a dimension time which has different levels (year, period – which is a 4 weeks period – and week).
The selections made in the time dimension affect the results of the numbers represented in my pivot: when only a year is selected the calculation works fine (the year is used to determine the previous year of course). When apart from that a period (1…13) OR a week (1…53) is selected the results are also fine.
But when I select a year and also a period AND a week, then the results are messed up.
CREATE MEMBER CURRENTCUBE.[Measures].[Gewicht Totaal VJ]
AS case
when [Periode].[Week].CurrentMember.Level.Ordinal=1
then sum(([Periode].[Jaar].Prevmember, [Periode].[Week].[1] : [Periode].[Week].[53]), [Measures].[Gewicht DJ])
when [Periode].[Periode].CurrentMember.Level.Ordinal=1
then sum(([Periode].[Jaar].Prevmember, [Periode].[Periode].[1] : [Periode].[Periode].[13]), [Measures].[Gewicht DJ])
when [Periode].[Jaar].CurrentMember.Level.Ordinal=1
then ([Periode].[Jaar].Prevmember, [Measures].[Gewicht DJ])
end,
FORMAT_STRING = “#,##0;-#,##0”,
NON_EMPTY_BEHAVIOR = { [Measures].[Gewicht DJ]},
VISIBLE = 1 , DISPLAY_FOLDER = ‘Totalen Vorig Jaar’ , ASSOCIATED_MEASURE_GROUP = ‘Verkopen’;
Can you please help me out on how to adapt this script in such a way that the correct numbers are displayed regardless of the selections made in the time dimension? A year total is of course always the same…
Thanks, your help is greatly appreciated!
Barry
Hi Barry
The problem is not the MDX, it’s what you’re trying to do. To calculate the previous year’s value, you need to have a single item selected. If you select both Period 1 and August, finding the previous year’s value doesn’t make any sense – they are two different values which can’t be shown in a single calculation.
Maybe you could explain in business terms why you need to have a multi-select, and how you’d go about calculating it manually. Then we could look to define an MDX calculation that replicates it.
Alex
Hi Alex,
Thank you for your reply! I understand it seems a bit strange what I’m describing: making date filters at two different levels. It’s something we ran into accidentally, so it is in fact not a normal user scenario. However, I think that the calculation should still be able to deliver the correct result as it is a year total.
Have a nice weekend!
Barry
Hey Alex,
I am really loving this thread but I am stuck on a part here. I see this part about being able to use two dates. I think this idea works for me. I have two dates one is Order Placed DAte and other is Status DAte. I would like it to default to Order Placed DAte and then if all is selected use Status Date. However I cannot figure out how to code this. Here is the code I have right now.
CREATE MEMBER CURRENTCUBE.[Measures].[% Growth (Order Date)] AS
//Checks for isempty and null condition sets them to null instead of #NUM!
IIF (([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]) = 0
,NULL
,IIF (([Order Placed Date].[Order Placed Date].CurrentMember, [Measures].[Total Cost]) = 0
,NULL
,(([Order Placed Date].[Order Placed Date].CurrentMember, [Measures].[Total Cost])
– ([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]))
/ ([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]))),
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = ‘Report’;
Then a second one that shows same thing but for Status DAte. Thus instead of having two seperate calculations I would like to see 1 single calculation. How to I change the above so the Scope works with it.
SCOPE([Date Comparison].[Comparison].[YTD Test]);
this = AGGREGATE(YTD([Ship Date].[Calendar].CurrentMember)
,[Date Comparison].[Comparison].DefaultMember);
SCOPE([Ship Date].[Calendar].[All]);
this = AGGREGATE(YTD([OrderDate].[Calendar].CurrentMember)
,[Date Comparison].[Comparison].DefaultMember);
END SCOPE;
END SCOPE;
Hi Alex,
I’m quite naive in MDX , I am trying to define a calculated member from an existing time hierarchy YWD , I need to populate the latest values of a month when month dimension is taken across column , currently I’m able to point to lastchild.lastchild on year ( eg: year 2010 week 5 day 31-01-2011) should return the value for last day of the month (31jan ) when taken across month dimension on columns , can you shread some light on this.. Thanks in advance
You can use the Tail function combined with Descendants, to find the last descendant of the current member of the date hierarchy.
Tail(Descendants([Date].[YWD].Currentmember, [Date].[YWD].[Date]), 1)
This returns a set with a single member, you can add .Item(0) if you want a member not a set.
Alternatively there is a shortcut, using the ClosingPeriod function.
ClosingPeriod(Date].[YWD].[Date], [Date].[YWD].Currentmember)
Regards
Alex
Hi Alex ,
Thanx for the advice ,but Sorry that wasn’t my requirement. Let me try to make it much clearer this time 🙂
I have created a calculated measure for date hierarchy YWD , which returns the latest value For each level by taking ordinal values in to account …
Here is how it works
Year 2011->
Week 52 ->
->dec26 – dec31
Query on year would return value at member dec31 , query on week 52 would return the same , hence latest across each week .
It works fine on YTD dimension , Now how can I bring in month dimension across , say on column across above example . so that i cold get value of day dec31 across month dec , value of nov30 across month Nov (which in turn is in week 48) ??
I don’t to what extend you got my scenario …
Could you advice !!!!
Hi Alex ,
As you have suggested , Can we populate the ClosingPeriod results on YWD hierarchy across month dimension ???. Could you please advice.
Hi
This isn’t a straightforward question to answer, I’d need to see the cube structure and look at it in a lot more detail to determine the best approach – there’s a limit to how complex one can get in blog comments I’m afraid.
For example it depends on the relationship between weeks and months (a week can span multiple months) etc.
So it sounds like you need to get someone in to take a close look at it and offer appropriate advice.
Good luck with it!
Alex
Thanks for your quick response.
I think it’s a simple cube with only week level on the fact table with start-schema design. Let me try your idea. If we have a big problems will offer you help on that.
Many thanks,
Thanh
Hi Alex,
Sorry for posting a new question,
I’m a beginner on SSAS Cube MDX, I have an urgent issue needs you advise.
I have one dimension named: DimProduct and one FactStores have a measurement named: NumberOfUnits
I want to write one MXD logic to set value for the NumberOfUnits
if DimProductKey=1 then FactStores.NumberOfUnits= FactStores.CurrentValue of FactStores.NumberOfUnits else set NumberOfUnits =0)
Any help would be appreciated!
Thanh
Your best option is to use ‘scope’ to change the definition of the calculation depending on the scenario.
In the Calculations tab of the cube designer, add something like the following:
SCOPE([Measures].[NumberOfUnits]
, EXCEPT([DimProduct].[DimProductKey].MEMBERS, [DimProduct].[DimProductKey].&[1]));
THIS = NULL;
END SCOPE;
This will override the calculation for all values of DimProductKey other than 1, setting their values to null.
Alternatively, rename the existing measure to [NumberOfUnits_Hidden] and hide it, then use scope to create a new measure
CREATE MEMBER CURRENTCUBE.[Measures].[NumberOfUnits]
AS NULL;
SCOPE([Measures].[NumberOfUnits]);
SCOPE([DimProduct].[DimProductKey].&[1]);
THIS = [Measures].[NumberOfUnits_Hidden];
END SCOPE;
SCOPE([DimProduct].[DimProductKey].[All]);
THIS = ([DimProduct].[DimProductKey].&[1]
, [Measures].[NumberOfUnits_Hidden]);
END SCOPE;
END SCOPE;
This allows you more control over how the measure is summarised, i.e. when the [All] member is selected, you can either manually aggregate the values of the children, or in this case you can just override [All] with the value for &[1], which will keep the calculation very fast.
Hi Alex,
Your suggestion has done with small changes. Thank you very much! And I have another question need your help.
I have a fact Fact_Orders has some measures as Ordered at week level. In the cube I want to create a derived column to keep previous value of ordered columns (same time grain)
It means that the cube has to including measures as previous_ordered, ordered (current order).
Please suggest me any idea!
Regards,
Thank
You can use the PrevMember of the date hierarchy to get the previous period’s measures in a calculated measure. Or use ParallelPeriod.
There’s plenty of examples on blogs, including here:
http://xzwang.wordpress.com/2013/08/13/a-better-way-to-write-mdx-period-over-period-calculations/
Regards
Alex
Thnx Alex ,much appreciated …
Hello Alex,
I want to create running totals within my cube for one of the distinct count measure that I have
And following table shows my measures and required running total measure values.
“Unique Users” measure is distinct count measure of users.
And “RunningUU” measure is just sums the numbers, but it should be distinct count of users.
My goal is to create running values similar to “Actual Running Total should be” measure in the following list.
Row Labels Unique Users RunningUU Actual Running Total Should be
FY08 8658 8658 8658
FY09 51175 59833 57000
FY10 62914 122747 85000
FY11 93702 216449 130000
FY12 119264 335713 190000
FY13 124156 459869 220000
FY14 93493 553362 268549
FY15 553362 268549
Grand Total 268549 268549 268549
Please let me know if anyone knows creating running totals for distinct count measures with time dimension.
Appreciate your help
You can achieve this by just creating a new calculation that aggregates the Unique Users measure over the set of all historic time members.
CREATE MEMBER CurrentCube.[Measures].[Running Total] AS
AGGREGATE({null:[Date].[Calendar].CurrentMember}
, [Measures].[Unique Users]);
This calculates the Unique User measure over the set of all dates from the beginning of time (null) through to the current selected member of the Calendar hierarchy in the Date dimension.
Good Evening Alex.
I am pretty new in SSAS 2008 R2.
I have a cube with a [Measures].[Sales Amount] and a Time Dimension [Calendar]
In Time Dimension i have a Hierarchy like this: Year (2014) – Week – Date (ie 15 June 2014)
I have also other hierarchies…such as YEAR-MONTH-DAYS .. and so on
My work collegues need to compare sales amount from 1 June 2014 to 14 June 2014
w i t h
parallel period of pevious year but NOT from 1 June 2013 to 14 June 2013
B u t
parallel period of previous year according the same type of days (MOnday with monday ,,,tuesday with tuesday and so on,…)
In my case i need to calculate from 2 June 2013 to 15 June 2013..
Is it possibile in SSAS to do that ???
I need other kind of hierarchies in my TIME DIMENSIONS ???
I think i need to use hierarchy YEAR-WEEK-DATE but i am so expert to think to that..
Any suggest or article to study ???
Thanks a lot.
Yes you’re right, you could only do this with the Year-Week-Date hierarchy, it wouldn’t make any sense when using the Year-Month-Date hierarchy.
Something like:
(ParallelPeriod([Calendar].[Year-Week-Date].[Year],1,
[Calendar].[Year-Week-Date].CurrentMember),[Measures].[Sales Amount])
This does mean that this calculation will not work when using months.
Hi Alex,
Thanks again for your valuable time.
I would like to ask you a question regarding this post.
I redesign my snapshots table in order to have just one date for the open status of the orders using a view. Now I followed your suggestions to make it works in to dimension, something like this:
CREATE MEMBER CURRENTCUBE.[Measures].[OOH Net Value]
AS aggregate(
{null:[Time].[Year – Week – Date].CurrentMember} * {null:[Planned Deliv Date].[Year – Week – Date].CurrentMember}
,[Measures].[OOH Net Value Domestic]);
It works fine, then next challenge is to calculate the Last Year Value. I did it like this:
CREATE MEMBER CURRENTCUBE.[Measures].[OOH Net LY] AS NULL;
SCOPE([Measures].[OOH Net LY]);
SCOPE([Planned Deliv Date].[Date].Members, [Time].[Date].Members);
this =
— Determine the time dimension to be used
iif([Planned Deliv Date].[Year – Week – Date].CurrentMember.Name = [Time].[Year – Week – Date].[AllDates].Name,
(
Cousin
(
[Time].[Year – Week – Date].CurrentMember
,Ancestor
(
[Time].[Year – Week – Date].CurrentMember
,[Time].[Year – Week – Date].[Year]
).Lag(1)
)
,[Measures].[OOH Net Value]
(
Cousin
(
[Nat Planned Sales Deliv Date].[Year – Week – Date].CurrentMember
,Ancestor
(
[Nat Planned Sales Deliv Date].[Year – Week – Date].CurrentMember
,[Nat Planned Sales Deliv Date].[Year – Week – Date].[Year]
).Lag(1)
)
,[Measures].[OOH Net Value]
)
); — end if and this
END SCOPE;
END SCOPE;
As you can see, the code has too many lines, it is repetitive and not especially easy to understand. Basically what I do is:
– Define an AllMemberName for my Time hierarchy [Year – Week -Date] (AllDates)
– Determine which is the selected hierarchy
– Calculate the Last Year value using the Cousin function
The script works, but I am not happy with it, so I summarized the requirements here:
– The script should works with both time dimensions
– Across every hierarchy (I have more than one)
– Only snapshots up to the current date should be shown (I already solved this requierement but I have not included to “simplify” the script)
May you help me to determine other possible solution?
Any comment would be appreciated.
Kind Regards,
Paul
Hi Alex,
I have different time dimensions… start time of the service, end time of the service and the time for the report.
I need to calculate:
Start time less or equal to currenttime AND ( End time is null OR greater then current time) for a measure?
I’m French speaking, so sorry for any spelling mistake
You can use something line this
SELECT {[Measures].[My Measure]} ON 0,
NON EMPTY {
[Service].[Service].[Service].MEMBERS
* {null:LINKMEMBER([Report Date].[Calendar].CURRENTMEMBER, [Service Start Date].[Calendar])}
* {LINKMEMBER([Report Date].[Calendar].CURRENTMEMBER, [Service End Date].[Calendar]):null}
}
ON 1
FROM [My Cube]
WHERE [Report Date].[Calendar].[Date].&[20140722]
Where you filter by a ‘Report Date’ dimension, which isn’t actually linked to the measure group. However you can use LinkMember to map the Report Date into the Service Start and Service End date dimensions.
Use this to create sets of dates for all start dates prior the report date {null:xx} and all end dates after the report dates {xxx:null}.
Cross Join them together and you will get a set of Service members matching the dates.
Regards
Alex
Dear Alex,
I have read through all the questions posed here and I am really impressed by your answers. Please can you help me in my situation, I am still learning OLAP cube and MDX. My scenario is as follows:
I have 2 date dimension hierarchies –
the first – Year-Quarter-Month-Week-Day
the second Year-Quarter-Month-Day
I needed to allows my user to select a date or range of date from a visual tool and then display the result as follows (this is just a subset of the fields):
Sales Amt, MTD Sales, YTD Sales, Prior Yr MTD Sales, Prior Yr YTD Sales
Now, lets say my user select 20th as their date filter for a month that has the full month data already, the expected result is MTD Sales for the first 20 day of the month, sames for the other aggregations.
Using the second date dimension hierarchy, I am able to achieve that. Using the first date dimension hierarchy, the result for MTD Sales is the total sales for the month and not the first 20 days as desired. Since the second date dimension hierarchy work for this, I am fine.
However, when user select a range of date, say 1st – 20th, then I am unable to get MTD Sales for the first 20 days, either with the first date dimension hierarchy or the second. Actually, I get error when I use the second dimension hierarchy, and get full month MTD Sales with the first dimension hierarchy.
Please what can I tweak to achieve my goal. Thanks in anticipation.
Gboyega
I Have 4 date dimensions with defined hierarchy . They all have different names so I cannot use the NULL.
I tried using the Scope method you described above and it works fine for some and not for others especially the YTD and prior YTD calculations. My YTD only shows last years month data and the Prior YTD shows previous month data. Not sure what I am doing wrong.
I created a shell date calculation dimension based on this url
http://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/
Here is part of my script
// YTD calculations
SCOPE([Date Calculation].[Date Calculation].MEMBERS);
[Date Calculation].[Date Calculation].[YTD] =
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[S Date].[S Date Yr].[S Date Yr],
[S Date].[S Date Yr].currentmember
)
);
SCOPE ([S Date].[S Date Yr].[All]);
[Date Calculation].[Date Calculation].[YTD] = AGGREGATE (
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[E DATE].[E Date Yr].[E Date Yr],
[E DATE].[E Date Yr].currentmember
)
);
SCOPE ([E DATE].[E Date Yr].[All]);
[Date Calculation].[Date Calculation].[YTD] =
AGGREGATE (
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[T DATE].[T Date Yr].[T Date Yr],
[T DATE].[T Date Yr].currentmember
)
);
SCOPE ([T DATE].[T Date Yr].[All]);
[Date Calculation].[Date Calculation].[YTD] = AGGREGATE (
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[P DATE].[P Date Yr].[P Date Yr],
[P DATE].[P Date Yr].currentmember
)
);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
SCOPE([Date Calculation].[Date Calculation].MEMBERS);
[Date Calculation].[Date Calculation].[PY YTD] =
IIF( AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[S Date].[S Date Yr].[S Date Yr],
PARALLELPERIOD
(
[S Date].[S Date Yr].[S Date Yr],
1,
[S Date].[S Date Yr].CURRENTMEMBER
)
)
) = 0 OR ( AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[S Date].[S Date Yr].[S Date Yr],
PARALLELPERIOD
(
[S Date].[S Date Yr].[S Date Yr],
1,
[S Date].[S Date Yr].CURRENTMEMBER
)
)
) ),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[S Date].[S Date Mo Yr].[S Date Mo Yr],
PARALLELPERIOD
(
[S Date].[S Date Mo Yr].[S Date Mo Yr],
11,
[S Date].[S Date Mo Yr].CURRENTMEMBER
)
)
),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[S Date].[S Date Yr].[S Date Yr],
PARALLELPERIOD
(
[S Date].[S Date Yr].[S Date Yr],
1,
[S Date].[S Date Yr].CURRENTMEMBER
)
)
));
SCOPE([S Date].[S Date Yr].[All],
[S Date].[S Date Mo Yr].[All]);
[Date Calculation].[Date Calculation].[PY YTD] =
IIF(
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[E DATE].[E Date Yr].[E Date Yr],
PARALLELPERIOD
(
[E DATE].[E Date Yr].[E Date Yr],
1,
[E DATE].[E Date Yr].CURRENTMEMBER
)
)
) = 0 OR (AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[E DATE].[E Date Yr].[E Date Yr],
PARALLELPERIOD
(
[E DATE].[E Date Yr].[E Date Yr],
1,
[E DATE].[E Date Yr].CURRENTMEMBER
)
)
)),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[E DATE].[E Date Mo Yr].[E Date Mo Yr],
PARALLELPERIOD
(
[E DATE].[E Date Mo Yr].[E Date Mo Yr],
11,
[E DATE].[E Date Mo Yr].CURRENTMEMBER
)
)
),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[E DATE].[E Date Yr].[E Date Yr],
PARALLELPERIOD
(
[E DATE].[E Date Yr].[E Date Yr],
1,
[E DATE].[E Date Yr].CURRENTMEMBER
)
)
))
;
SCOPE([E DATE].[E Date Yr].[All],
[E DATE].[E Date Mo Yr].[All]);
[Date Calculation].[Date Calculation].[PY YTD] =
IIF(
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[T DATE].[T Date Yr].[T Date Yr],
PARALLELPERIOD
(
[T DATE].[T Date Yr].[T Date Yr],
11,
[T DATE].[T Date Yr].CURRENTMEMBER
)
)
) = 0 OR IsEmpty(AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[T DATE].[T Date Yr].[T Date Yr],
PARALLELPERIOD
(
[T DATE].[T Date Yr].[T Date Yr],
11,
[T DATE].[T Date Yr].CURRENTMEMBER
)
)
)),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[T DATE].[T Date Mo Yr].[T Date Mo Yr],
PARALLELPERIOD
(
[T DATE].[T Date Mo Yr].[T Date Mo Yr],
11,
[T DATE].[T Date Mo Yr].CURRENTMEMBER
)
)
),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[T DATE].[T Date Yr].[T Date Yr],
PARALLELPERIOD
(
[T DATE].[T Date Yr].[T Date Yr],
11,
[T DATE].[T Date Yr].CURRENTMEMBER
)
)
) )
;
SCOPE([T DATE].[T Date Yr].[All],
[T DATE].[T Date Mo Yr].[All]);
[Date Calculation].[Date Calculation].[PY YTD] =
IIF(
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[P DATE].[P Date Yr].[P Date Yr],
PARALLELPERIOD
(
[P DATE].[P Date Yr].[P Date Yr],
1,
[P DATE].[P Date Yr].CURRENTMEMBER
)
)
) = 0 OR IsEmpty(AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[P DATE].[P Date Yr].[P Date Yr],
PARALLELPERIOD
(
[P DATE].[P Date Yr].[P Date Yr],
1,
[P DATE].[P Date Yr].CURRENTMEMBER
)
)
) ),
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[P DATE].[P Date Mo Yr].[P Date Mo Yr],
PARALLELPERIOD
(
[P DATE].[P Date Mo Yr].[P Date Mo Yr],
11,
[P DATE].[P Date Mo Yr].CURRENTMEMBER
)
)
),
AGGREGATE
(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
(
[P DATE].[P Date Yr].[P Date Yr],
PARALLELPERIOD
(
[P DATE].[P Date Yr].[P Date Yr],
1,
[P DATE].[P Date Yr].CURRENTMEMBER
)
)
) )
;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
When the end user selects both [YR] and [MO YR] dates we can see the YTD and the PY YTD but this is only calculating monthly values. They want to be able to select either YR or MO YR or a combination of both and still get all YTD and PY YTD.
What am I doing wrong?
Thank you.
When the end user selects a
Hi Tonia
Thanks for the comment, however I’m afraid this is too in depth to be able to debug and diagnose this via a blog. I’d recommend that you engage an SSAS/MDX consultant to review this and investigate options. Quite often problems like this can be solved by alterations to the data model or cube structure, without having to do as much work in MDX. Where possible, this is always a preferable solution and hence why I’m reluctant to offer advice without understanding more details of the system.
Hope this makes sense
Regards
Alex
Thank you again for the feedback. I appreciate it… Tonia
Hi Alex,
I’ve wrote something like below to the sum from starting to the selected date.
CREATE MEMBER CURRENTCUBE.[Measures].[AmountCur]
AS
SUM({NULL:[Time].[Years Quarters Months Weeks Days].CurrentMember}
, [Measures].[AmountCur]
);
But I’m not getting the correct result.
Can you help me out here.
Thanks,
Rakesh
Hi Alex!
A simple question from a newby:
Is there a way to create a calculated member/measure which would be based on the set of the non null months in the Date hierarchy and the measure value? The DW was design in such way that the month attribute has null value in case the row belongs to quarter or year in the Date dimension. Like this:
Id,Year,Quater,Month
1,2014,null,null,
2,2014,null,1
Regards,
Gabor
Hi Gabor
I’m really not a fan of including NULLs in member values in a cube. Personally I’d use 0 as a value for Qtr/Month that don’t exist.
Then the simplest option is to just deduct the 0 month from the overall total value:
[Measures].[My Measure] – ([Measures].[My Measure], [Date].[Month].[0])
You can get more creative with the MDX, there are many ways of achieving this, but this method keeps it simple and will not interfere with any other attribute filters you may have applied.
Regards
Alex
Hi Alex,
I fixed the issue above by defining heirarchy for each dimemnsion. Now the YTDs and PY YTD are calculating correctly.
I have another question. Within the same dimension I have Month Name and Month Number that the end users wants to be able to apply dynamic date calculations to as well. The heirarchy I created to fix my previous issue was
Date.YR
Date.QTR-YR
Date.MTH-YR
There are also MTH-Name and MTH-NBR in this dimemsion but I am unable to add this to the hierarchy. Each time I do I get an error.
Is there a way to link the MTH-Nme to the YR with out changing the structure. When I try something like this
WITH MEMBER [Date Calculation].[Date Calculation].[PY YTD] AS
AGGREGATE(
{[Date Calculation].[Date Calculation].[Selected Date]} *
PERIODSTODATE
( [DATE].[MTH-Name].[MTH-Name],
PARALLELPERIOD
([DATE].[MTH-Name].[MTH-Name],
1, {crossjoin([DATE].[MTH-Name].[MTH-Name],
[DATE].[Date-Hierarchy].[YR],)})
))
I get #Error .
What am I doing wrong? Or is this even possible to do? Thank you.
Hi Tonia
I’m not really sure I understand what you’re trying to do – and I really would need to see the cube to understand the details of what you’re trying to do and the problems you’re encountering. Just seeing a small section of MDX in isolation is not enough to understand the problem.
One problem I do see though is if MTH-Name contains Jan, Feb, Mar, etc., then you can’t do a PeriodsToDate or ParallelPeriod on it, as it’s not a specific date. Jan could be Jan 2014, Jan 2015, etc. So date functions will not work.
Instead, keep doing the calculations on the MTH-YR attribute, add an attribute relationship between MTH-YR and MTH-NAME, then you can access the month name as a property of the MTH-YR member
using something like [Date].[MTH-YR].CurrentMember.Properties(“MTH-Name”)
Regards
Alex
Thank you Alex. I will try to access the month name as a property of the MTH-YR member.
I will let you know if this works.
Hi Alex,
0
Sign in to vote
Dear David,
In Date hierarchy.
Sum({Null:[Date].[Year – Month – Date].CURRENTMEMBER},[Measures].[AmountCur])
When I selected the date from 1st Jan 2013 to 31st Jan 2013. It is taking 31stJan and calculating the Sum from Null to selected date. However, In my case it should take the 1st jan to calculate the Sum from Null to selected date.
For eg: when I select the date as Jan month or say Jan 5th to Jan 31st
For now it is taking as Where Date < 31-Jan. But in my case it should take the date as Date < 1-Jan if I selected total jan month
or Date < 5-Jan , if I select Jan5th – 31Jan .
How to achieve this?
Plz Suggest.
Thanks in advance.
Regards,
Rakesh
Hi Rakesh
Not sure who David is – are you on the right blog? Or just copying and posting the same question everywhere?
Anyway, CURRENTMEMBER is a single member, not a set. So why are you selecting a set of dates in the first place?
And how are you selecting this set of dates?
Regards
Alex
Hi Alex
Can you help? I’m using a PeriodsToDate function to calc MonthToDate of Gl Transactions. I’d like to use multiple calendar hierarchies.
Using your example above I’ve tried. I always get Value# whenever I query my cube.
Below is my calculation, can you guide me to the correct way of achieving this? Hopefully I am making a simple mistake with me syntax!
CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
AS Aggregate
(
PeriodsToDate
(
{NULL:[Times].[Natural Calendar CCL].[Calendar Month]
, [Times].[Natural Calendar CCL].CurrentMember}
* {NULL:[Times].[Fiscal Calendar CCL].[Fiscal Month]
, [Times].[Fiscal Calendar CCL].CurrentMember}
* {NULL:[Times].[Scirt Calendar CCL].[Scirt Calendar Month]
,[Times].[Scirt Calendar CCL].CurrentMember}
)
,[Measures].[Gl Amount]
),
FORMAT_STRING = “#,##0.00;(#,##0.00);0;0”,
NON_EMPTY_BEHAVIOR = { [Gl Amount] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;
Regards
Read
Hi Read
You’re passing invalid parameters to the PeriodsToDate function. PeriodsToDate requires a level and a member. You’re passing a crossjoined set.
PeriodsToDate can be replaced with the range operator ‘:’, so you usually don’t need them both.
You need to do three different PeriodsToDate, each on a different hierarchy. Then crossjoin the results, passing that to the Aggregate function.
Something like this will hopefully work, although it’s untested!
CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
AS Aggregate
(
PeriodsToDate
([Times].[Natural Calendar CCL].[Calendar Month]
, [Times].[Natural Calendar CCL].CurrentMember)
* PeriodsToDate
([Times].[Fiscal Calendar CCL].[Fiscal Month]
, [Times].[Fiscal Calendar CCL].CurrentMember)
* PeriodsToDate
([Times].[Scirt Calendar CCL].[Scirt Calendar Month]
, [Times].[Scirt Calendar CCL].CurrentMember)
,[Measures].[Gl Amount]
),
FORMAT_STRING = “#,##0.00;(#,##0.00);0;0″,
NON_EMPTY_BEHAVIOR = { [Gl Amount] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;
Hi Alex
Thanks for your suggestion. I tried this approach before contacting you, without success. Unfortunately your example returns #Value!.
I have resulted to scoping and found a solution that works, though is not as flexible as your suggestion. I would really like to try and find a solution along the lines you suggested above. This would allow me to use other calculations within these calcs.
Can you give me your thoughts on what might be wrong with this approach?
Regards
Read
//Copied from your post
CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
AS Aggregate
(
PeriodsToDate
([Times].[Natural Calendar CCL].[Calendar Month]
, [Times].[Natural Calendar CCL].CurrentMember)
* PeriodsToDate
([Times].[Fiscal Calendar CCL].[Fiscal Month]
, [Times].[Fiscal Calendar CCL].CurrentMember)
* PeriodsToDate
([Times].[Scirt Calendar CCL].[Scirt Calendar Month]
, [Times].[Scirt Calendar CCL].CurrentMember)
,[Measures].[Gl Amount]
),
FORMAT_STRING = “#,##0.00;(#,##0.00);0;0″,
NON_EMPTY_BEHAVIOR = { [Gl Amount] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;
Hi Alex!
Thanks for the help!
Regards,
Gabor
Hello Folks ,
I connected to cube through excel ,
I filter pane i selected date , I Select number of dates Let say
2015-01-01,
2015-01-05,
2015-01-06,
2015-01-07
Now i want to calculate the number of days between min date and max dates between selected dates in my cube
Thanks
Hi Sanjeewan
I’m not aware of any way of doing this I’m afraid.
If you do find away, please do take the time to let me know here, I’d be interested to know.
Regards
Alex
To do this in Adventure work
create dynamic set [SelectedDates] as
existing [Date].[Date].[Date].members;
create member currentcube.measures.DaysBetweenMinAndMaxDates as
count(SelectedDates.item(0):tail(SelectedDates).item(0));
Hi,
Considering two role playing dimension [Order Date], [Issue Volume Date] how to return Y-1 value regardless of the dimension selected?
I tried define calculation in scopes
([Order Date].[Year-Month].members,[Measures].[Amount Y-1])=
(ParallelPeriod([Order Date].[Year-Month].[Calendar Year],1,[Order Date].[Year-Month]), [MEASURES].[Amount]);
//–iv dimension
([Issue Volume Date].[Year-Month].members,[Measures].[Amount Y-1])=
(ParallelPeriod([Issue Volume Date].[Year-Month].[Calendar Year],1,[Issue Volume Date].[Year-Month]),[MEASURES].[Amount])
;
But it seems that the second overwrite the first.
Thank you for suggestion.
Best,
B.
[Order Date].[Year-Month].members and [Issue Volume Date].[Year-Month].members will both include the ‘All’ member. Therefore the second scope will always override the first as there will always be a valid selection in the second scope, either a specific member, or the All member.
To get around this, replace the members statements with
[Order Date].[Year-Month].[Year-Month].members
and
[Issue Volume Date].[Year-Month].[Year-Month].members
Hopefully that will fix it.
Alex
Hi,
Thank you!!! Works perfectly for [Year-Month]
for [Year] Level I used
[Issue Volume Date].[Year-Month].[Year].members respectively
and works like charm too.
Best,
B.
Hi Alex,
Thanks a lot for the excellent write up! It was very useful 🙂 I would like to check if it is possible to do the below scenario
I’ve to show the Opening balance by year for all the months in that year. For eg.
Year Opening Balance
201301 $1000
201302 $1000
.
.
201312 $1000
For 2014 I’ll receive new balance and would like to show them as Opening balance for all 2014 months
201401 $1500
.
.
201412 $1500
Please let me know if you need any additional information.
Thanks a lot for your help!
Sankar
The best way to approach this would be to navigate around the hierarchy.
From a month, you can identify the first month in the year by using
[Date].[Year Month Hierarchy].CurrentMember.Parent.FirstChild
Which first of all goes up one level to the Year level, and then returns the first child (January).
Alternatively there’s a shortcut using FirstSibling:
[Date].[Year Month Hierarchy].CurrentMember.FirstSibling
Once you have this, just query the opening balance for the member that’s returned:
([Measures].[Opening Balance], [Date].[Year Month Hierarchy].CurrentMember.FirstSibling)
Regards
Alex
Thanks a lot for the quick response!!
I tried the below but repeats the YTD balance of each month as opening balance.
With
MEMBER [Measures].[UTP OB] AS
([Measures].[UTP Amount], [Time].[Hierarchy].CurrentMember.FirstSibling)
Select {
[Measures].[UTP Amount],
[Measures].[UTP OB],
} ON COLUMNS ,
{[Time].[Year Month].[Year Month] } ON ROWS
From AITRA
I used the below before and worked fine for 2014 balances. With 2015 balances in FACT It just repeats 2014 balances.
AGGREGATE(
{NULL:[Time].[Year Month].CurrentMember}
, [Measures].[OB UTP Amount]
)
Thanks again!
I’m assuming that your calculated measure was actually the following: (typo in your post)
MEMBER [Measures].[UTP OB] AS
([Measures].[UTP Amount], [Time].[Year Month].CurrentMember.FirstSibling)
If so, this will only work if the Year Month hierarchy has (at least) two levels in it; Year and YearMonth.
From what you describe, it sounds like it only has a single level (YearMonth).
If you change this in your dimension design then it should work correctly for all years.
The Time hierarchy has Year, Year Month and Date with it. Apologies for not making it clear. I should have explained it better.
I have 2 Measures UTP Amount and UTP Balance. The second measure is a manually populated in the table once in a year with the closing balance. For instance UTP Balance for 2014 is loaded as closing balance in 201312 period. For all months of 2014, this balance will be displayed. Similarly for 2015, a one time balance is loaded in 201412 under UTP Balance field and is expected to show this balance for entire 2015 without aggregation.
I’m trying to achieve this with a calculated measure. Hope I’ve explained it better.
Thanks a lot!
Hi Alex,
I’m new to MDX and I’m in process of automation one report which user generate everyday manually using base cube data. he fetch data in one sheet from cube and map in main sheet as formula.
The complexity is to create the dynamic calculated measures of month wise transactions columns with putting last year same month value of each device. Measure is available in cube which is transactions count.
Reports grow horizontally and user wants to compare each device transactions count comparing with previous year months or day with current year month and day.
Reports consideration period is Jan2014-Dec 2015. current months data shows date wise but once month complete measured rolls up for that month and create new column for completed months and also place last year month columns to see comparison.
here is the report format.
Device ID | description | Jan-14 | Jan-15 |1st feb 14 | 1st feb 15| 24feb14|2feb15|feb14 MTD|feb15 MTD..
Each day, new column will populate for that day for current year as well as for last year, once feb complete, it will populate two columns like Feb-14 | Feb-15. This will go on until Dec 15
Can i achieve this dynamics field creation through MDX query. any sample mdx query
Looking forward to hear from you.
Thanks,
Niel
Hi Niel
Difficult to explain the process, so better with an example.
If you run the following query against Adventure Works cube it should give you the results you’re looking for.
Then it’s just a case of modifying it to work with your cube structure.
WITH MEMBER [Measures].[Prev Yr] AS
(PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount])
MEMBER [Measures].[Column Name] AS
[Date].[Calendar].CurrentMember.MEMBER_NAME
SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
* {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
ON 0
,[Product].[Product].[Product].MEMBERS ON 1
FROM [Adventure Works]
Regards
Alex
Thanks for your quick respose but provided query is showing product on row, there is no respective pre yr or internet sales calculation.
Can you please resend the query.
Thanks,
Niel
Hi Niel
The query has products listed on the rows, and the months/days on the columns. Each month/day has two columns, one for the current period, one for the previous year.
Just run the query in Management Studio against Adventure Works and you’ll see what I mean.
Regards
Alex
Hi Alex,
MDX query results work fine as i need to populate such data in Cube so even they can slice and dice without report too.
Can i convert this query into calculated members in Cube?
Please let me know the logic.
Thanks,
Niel
Sure, just add the following into the calculations tab of the cube
CREATE MEMBER CURRENTCUBE.[Measures].[Previous Year Internet Sales Amount]
AS (PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount]);
Hi Alex,
I’ve added calculation but don’t see same previous & current year aggregation month, and current & previous year months day wise transactions.
I wan to see same results which below query display, so user can simply drag against product.
Can you please advise how to generate similar columns in Cube.
WITH MEMBER [Measures].[Prev Yr] AS
(PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount])
MEMBER [Measures].[Column Name] AS
[Date].[Calendar].CurrentMember.MEMBER_NAME
SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
* {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
ON 0
,[Product].[Product].[Product].MEMBERS ON 1
FROM [Adventure Works]
Thanks,
Niel
Embedding cube calculations into the cube are just that; calculations. You cannot embed a report structure or entire query into the cube.
You have to write the query in MDX, or drag and drop in Excel etc.
The embedded measures just provide predefined calculations to simplify the process.
In Excel, you would have to drag in the relevant measures (‘Internet Sales Amount’ and ‘Previous Year Internet Sales Amount’)
Then drag in the rows & columns; i.e. the products, and the relevant members of the date dimension.
You could create a named set within the cube to make the date selection more simple, but you’d still need to manually drag it into the pivot.
Regards
Alex
Hi Alex,
Sorry for the trouble again but if you can explain would be great based on your experties in MDX.
I’ve one more difficulty to use below MDX/even i create calculation in the cube level. This query fetch the last year month total and current year month total so it’s consider complete 30/31 days.
User has 2 more checks that’s need to be managed while getting each month total and those are below.
(1) I’ve to exclude if there are any public holiday in previous/current year month(i’ve holiday table).
(2) If first day of month starts on Sat/Sunday, then sum should be consider from next day. Let’s take example of Feb 2015 & 2014. Feb 1st 2015 came on Sunday, so what user does he go back and look for Feb 2014 1st day, that was fall in Saturday so he will exclude 1st & 2nd feb of last year and start sum of transaction from 3-2-2014 to 28-2-2014
For current year 2015 will consider same from 3-2-2015 to 28-2-2015
User based to start date preevious year always.
Basically, weekdend always going good sale that’s can’t consider if 1st day start on Weekend and secondly, we should avoid considering public holiday too.
Is this achievable through MDX / cube calculation level? I’m flat out to go either of this option and considering T-SQL stored procedure to intorduce such logic
Let me know your thoughts.
Thanks,
Niel
WITH MEMBER [Measures].[Prev Yr] AS
(PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount])
MEMBER [Measures].[Column Name] AS
[Date].[Calendar].CurrentMember.MEMBER_NAME
SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
* {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
ON 0
,[Product].[Product].[Product].MEMBERS ON 1
FROM [Adventure Works]
You need to add an extra attribute to the Date dimension, populated in the ETL layer or DSV.
This attribute would indicate whether each date should be included in these calculations, based on the day of week/holiday of the date and the previous years date.
Then you can simply filter the MDX query by this attribute.
Regards
Alex
Just noticed an extra requirement that I missed originally – only expand current month, leaving previous months aggregated to the month level.
The best way to achieve this is to have a new ‘Current Month’ attribute in the date dimension, which you can use to filter in/out the current month, to use different logic for each.
Then expand my test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]
Hi Alex,
Sorry for the trouble again, but I’m completely new for MDX. Hope you understand me as newbie in MDX world.
Can you please describe the sample query to add ‘Current Month’ and i’ve already “cal_month_no” in my dim_date dimension. Also, if i want we can combine all static attributes(like name, contract expiry year, annualised revenue,zero months in last 12, consecutive zero months, relationship manager name, total txn count week end etc) with cross join in this query, right?
Here is my dim_date table, if i take exmaple of todays date which is pre-populated
cal_day_in_week_no
5
dim_date_key
20150226
calendar_date
2015-02-26 00:00:00.000
cal_day_in_week
Thu
cal_day_in_month
26
cal_month_no
2
Thanks in advance and looking forward to hearing from you.
Cheers,
Niel
The easiest way is to go into your data source view, right click on the date table, and ‘Create Named Calculation’.
Call this something like ‘Is Current Month’, with an expression of:
CASE WHEN [Year]=YEAR(GETDATE()) AND [Month]=MONTH(GETDATE()) THEN ‘Yes’ ELSE ‘No’ END
(replacing [Year] and [Month] with the year and month from your date dimension)
Then go to the Date dimension, and add this new field in as an attribute, which can then be used in your queries.
To add extra attributes into the report, just cross join them with the set of products in my original query. Don’t forget to include a NON EMPTY clause though to remove invalid combinations.
You can change the rows to show anything you like, the query will still work.
Regards
Alex
Thanks a lot Alex, I’m really appreciate your effort to get this solution.
I’ve executed provided query and its fetching exactly month/day for Prv yr and current year.
I’ve also created current month attribute in date dimension to filter in/out current month and after adding attribute, I have explored dimension data which perfectly set yes for current month and rest are NO.
**Current month attribute: [Date].[Ls Current Month].[Yes]
Here few queries if you can clarified would be great:
1) Query results showing as expected but even for Prev Yr month/day, year populate as 2008 but I need it as 2007(or one year less that provided member value)
2) As you said to expand your test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]
Do I need to replace below section or top of existing query should I use filter?
“SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008]”
if possible can you please let me know how to use this filter and union?
3) Is that possible to populate below 2more columns in same query, this need to compare with last year. If I am preparing report today 26th Feb then MTD will run for period(1st feb-25feb)
(1) MTD for getdate()-1(because report run for last processing day)
(2) MTD for same day last year
Looking forward to her from you.
Thanks,
Nilesh
Hello Alex,
I’ve requirement where my measure comes from named query and those measure column name changes every day/months(some of remain constant, others add new), so if I create measure group based on this named query table.
Does it create automatic every day while processing cube? I tried it but if underlying query populate more measure column and change existing measure name then processing was giving error.
I think, if I want to achieve this I have to drop and re-create all measure of this group dynamically through XMLA scripts? even based on this measures, I’ve to add some calculation too but I am not sure how to do this.
Can you please let me know the best way to achieve this results. I may call this script in my daily ETL load job before cube start full processing.
Appreciate, if you can give a me small example with script based on adventureworks cube.
Looking forward to hear from you.
Thanks,
Niel
I would seriously question the approach of changing measure names on a daily basis – I don’t know the details or justification, but it sounds like a very problematic approach. I’d recommend revisiting this and finding a less volatile solution to the problem…
Thank you Alex on responding my all queries so promptly.
Appreciate your all time to advise such great solution.
Is there systematic steps by step way to learn/improve MDX knowledge? I’m completely new to MDX and its been good learning from your blog so far.
Let me know if any books or best links I should follow.
Regards,
Niel
It’s not a simple query language to learn, it takes time and patience. However I’d heavily recommend the following:
– MDX courses by technitrain.com
– ‘Fast Track to MDX’ book. It’s old and expensive, but very good
– and of course blogs; mine, Chris Webb, Alberto Ferrari, Marco Russo, etc.
But the main thing is trial and error – start out simple, and progress. If your MDX is to complex, remodel your data to simplify the cube.
Hi Alex,
I have tried numerous approaches, reached the point past frustration and this is the calm before I pick up the PC and throw it out the window… this is my most recent version, please help for the PC’s sake 🙂
I need to define the income received in the first year on book, then the income in the second year on book,… lastly and the total income received from 4 years onwards.
Ideally, instead of defining income within the time frames as above, I could have a dimension which can be applied to the various measures but this approach is further than what I currently have.
//////////////////////////////////////////////////////
With
Member [Measures].[Income – Year 0] As
([Measures].[Income]
, LinkMember( [Sales Period].[Sales Year]
, [Book Period].[Book Year]
)
)
Member [Measures].[Income – Year 1] As
([Measures].[Income]
, LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
, -1
, [Sales Period].[Sales Year]
)
, [Book Period].[Book Year].[Book Year]
)
)
Member [Measures].[Income – Year 2] As
([Measures].[Income]
, LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
, -2
, [Sales Period].[Sales Year]
)
, [Book Period].[Book Year].[Book Year]
)
)
Member [Measures].[Income – Year 3] As
([Measures].[Income]
, LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
, -3
, [Sales Period].[Sales Year]
)
, [Book Period].[Book Year].[Book Year]
)
)
Member [Measures].[Income – Year 4] As
([Measures].[Income]
, {LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
, -4
, [Sales Period].[Sales Year]
)
, [Book Period].[Book Year].[Book Year]
)
: Null}
)
Select
AddCalculatedMembers(Measures.Members) on Columns
, non empty [Sales Period].[Sales Year].[Sales Year] on Rows
From [My Cube]
;
//////////////////////////////////////////////////////
The result is correct for [Measures].[Income – Year 0] however I get a #Error for the remaining calculated measures.
Kindly kindly help!
Hi Alex… any insight regarding previous post?
Sorry for delay – I was away all last week, trying to catch up on the blog this week…
Try this…
//////////////////////////////////////////////////////
With
Member [Measures].[Income – Year 0] As
([Measures].[Income]
, LinkMember( [Sales Period].[Sales Year].CurrentMember
, [Book Period].[Book Year]
)
)
Member [Measures].[Income – Year 1] As
([Measures].[Income]
, LinkMember( [Sales Period].[Sales Year].CurrentMember
, [Book Period].[Book Year]).LAG(1)
)
Member [Measures].[Income – Year 2] As
([Measures].[Income]
, LinkMember( [Sales Period].[Sales Year].CurrentMember
, [Book Period].[Book Year]).LAG(2)
)
Member [Measures].[Income – Year 3] As
([Measures].[Income]
, LinkMember( [Sales Period].[Sales Year].CurrentMember
, [Book Period].[Book Year]).LAG(3)
)
Member [Measures].[Income – Year 4] As
AGGREGATE(
{ LinkMember( [Sales Period].[Sales Year].CurrentMember
, [Book Period].[Book Year]).LAG(4):null}
,[Measures].[Income])
Select
AddCalculatedMembers(Measures.Members) on Columns
, non empty [Sales Period].[Sales Year].[Sales Year].MEMBERS on Rows
From [My Cube]
;
//////////////////////////////////////////////////////
Hi Alex,
Can you please correct my below MDX FILTER function using query, it worked fine without FILTER. As per your reply on 3rd March i’ve populated an extra attribute to my settlement Date dimension which indicate what date needs to be consider for calulcation(there is flag against each date ‘y/n’). I apply that filter in my MDX but its only shows [Terminal].[Agreement Identifier].
Also, I want to use another filter from same settlement date dimension where ‘ls current month’ Named Calculation created with an expression of:
CASE WHEN [Year]=YEAR(GETDATE()) AND [Month]=MONTH(GETDATE()) THEN ‘Yes’ ELSE ‘No’ END
(this is your original advise: Just noticed an extra requirement that I missed originally – only expand current month, leaving previous months aggregated to the month level.
The best way to achieve this is to have a new ‘Current Month’ attribute in the date dimension, which you can use to filter in/out the current month, to use different logic for each.
Then expand my test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]
)
MDX Query :
————–
WITH MEMBER [Measures].[Prev Yr Txn]
AS
(PARALLELPERIOD([Settlement Date].[Calendar].[Settlement Calendar Year], 1, [Settlement Date].[Calendar].CurrentMember)
,[Measures].[Transaction Count])
MEMBER [Measures].[Column Name] AS
[Settlement Date].[Calendar].CurrentMember.MEMBER_NAME
SELECT
NON EMPTY
DRILLDOWNLEVEL(
FILTER(
DESCENDANTS([Settlement Date].[Calendar].[Settlement Calendar Year].&[2013],
[Settlement Date].[Calendar].[Settlement Calendar Month]),
[Settlement Date].[Settlement Tpdd Cal Flag].&[Y])
* {[Measures].[Prev Yr Txn], [Measures].[Transaction Count]}) ON 0,
[Terminal].[Agreement Identifier].MEMBERS ON 1
FROM [cube_transaction_hourly];
Looking forward to receive expert solution from you to resolve my issue.
Thanks,
Niel
Really difficult to debug MDX when you can’t run or test it, but try this…
WITH MEMBER [Measures].[Prev Yr Txn]
AS
(PARALLELPERIOD([Settlement Date].[Calendar].[Settlement Calendar Year], 1, [Settlement Date].[Calendar].CurrentMember)
,[Measures].[Transaction Count])
MEMBER [Measures].[Column Name] AS
[Settlement Date].[Calendar].CurrentMember.MEMBER_NAME
SELECT
NON EMPTY
DRILLDOWNLEVEL(
FILTER(
DESCENDANTS([Settlement Date].[Calendar].[Settlement Calendar Year].&[2013],
[Settlement Date].[Calendar].[Settlement Calendar Month]),
[Settlement Date].[Settlement Tpdd Cal Flag].&[Y]))
* {[Measures].[Prev Yr Txn], [Measures].[Transaction Count]} ON 0,
[Terminal].[Agreement Identifier].MEMBERS ON 1
FROM [cube_transaction_hourly];
Thanks Alex, it perfectly worked even i tried with WHERE clause and both results are same.
…..WHERE([Settlement Date].[Settlement Tpdd Cal Flag].&[Y])
when I connect to my cube , I select a field in a dimension and apply filter in excel pivot table, it does not display row labels. It will only display row labels if I select a measure. How do I make it apply the filter and just display the row label name. I will appreciate any advice.
Hi Tonia
You just need to right click on the pivot table, select ‘Pivot Table Options’, then the ‘Display’ tab.
Select ‘Show items with no data on rows’, and the members should be displayed.
The pivot table essentially does it’s own NON EMPTY functionality. This setting overrides it.
Regards
Alex
Thank you Alex!
Tonia
Hi Alex,
I would like to build following Calculated members, Can you please give sample MDX to get this reullts if you can?
It wuld okay, if you can give example based on AdventureWorks database, internet sales amt for products etc.
1. Total Transactions count Weekend Sunday (i’ve date dimesion which holds day_in_week=’Sun’ for respetive dates)
2. YTD Transactions = Transaction count year to date
3. YTD Days = Count of days within calendar year to date
4. Zero months in last 12 = Number of zero transacting months in the last 12 months rolling window
5. Consecutive zero months = Number of consecutive zero transacting months in last 12 months rolling window
Looking forward to hear from you some good advise.
Thanks,
Niel
Hi Niel
This blog is to provide help when people get stuck on a problem, unfortunately I’m unable to do development work for you via this blog, or offer continuous help on every problem you encounter. If you need this level of help then I’d heavily recommend engaging the services of a good BI consultant who can provide you with dedicated one to one training and advice. This is due to the fact that fixing an MDX problem well requires a deeper understanding of data modelling and an understanding of the whole cube, which is almost impossible via a blog post. Therefore there’s a very good chance that I may be giving you sub-optimal advice, leading to further problems down the line.
I hope this makes sense, and good luck with the project.
Regards
Alex
No worries Alex, agreed.
Thanks for your time and help, have a great time ahead.
Hello Alex,
I’ve built below query to get Annualised Revenue(SUM of revenue for last 12 complete months) for each terminal device but its showing only terminal names, Annualised Revenue shows (null).
Any correction needed for this MDX Query? bascially i wwant to convert it as Calculated member
Here is ther query:
WITH MEMBER [Measures].[Annualised Revenue]
AS SUM(ClosingPeriod([Invoice Date].[Calendar Month].[Invoice Calendar Month],
[Invoice Date].[Calendar Month].[All Periods]).Lag(12)
: ClosingPeriod([Invoice Date].[Calendar Month].[Invoice Calendar Month],
[Invoice Date].[Calendar Month].[All Periods]),
[Measures].[Amount])
SELECT [Measures].[Annualised Revenue] ON 0,
[Terminal].[Terminal ID].MEMBERS on 1
FROM [cube_txn]
Do i need to add where cluase as “where ( [Invoice Date].[Calendar Month].[Invoice Calendar Year].&[2013])”
Your help will be much appreciated.
Thanks,
Niel
Hi Alex,
I am new to MDX, but I have written the following statement which achieves what I want it to, namely it checks that the opening value of a balance sheet account is or is not zero and if it is it calculates future balances based on predicted cash receipts and payments. However, it could be better. For example, I don’t particularly want to show historical balances. So, is there a way of working out which period I am in today, in order to be able ‘know’ what is in the future and what is in the past?
Thanks Mark
SCOPE ([Time].[Time].[Time_L5].Members);
[Account].[Account].[Account_L4].&[829] = IIF([Account].[Account].[Account_L4].&[829] = 0,
(PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-CASH]) +
(PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-REC]) +
(PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-PAY]) +
(PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-JC]) +
(PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-BC]),
[Account].[Account].[Account_L4].&[829]);
END sCOPE;
Hi Mark
My preferred option is to add a new attribute into the date dimension. Either in the DSV or (preferred) in the source view for the date dimension. Something like:
CASE WHEN [Date]>GetDate() THEN ‘Future’ ELSE ‘Past’ END AS DateFuturePast
Then so long as you process the date dimension after midnight every day, it will always give you a really easy way of filtering dates without having to bother with complex MDX or dynamic sets etc.
Regards
Alex
Wow! Thanks for the swift reply. Forgive me, but what is a DSV? And by source view do you mean do this at the ETL stage?
One more question, I know I can write that case statement in T-SQL but is it valid in MDX as well?
Thanks Mark
The DSV is the Data Source View – which you’ll find in your Solution Explorer window.
You could write the expression as MDX, but then it would have to evaluate at runtime for every query, and you couldn’t make use of the attribute in aggregations etc.
So for this case I would write it in SQL so it’s calculated at processing time. The DSV is simply a query which runs against the data warehouse at processing time, and is used to populate the dimension or measure group.
Adding the calculation to the DSV would effectively turn the cube source query into something like this:
SELECT *
, CASE WHEN [Date]>GetDate() THEN ‘Future’ ELSE ‘Past’ END AS DateFuturePast
FROM dim.Date
Yes you could calculate it in the ETL process instead, but I don’t see the benefit as it would force you to run the ETL every night when you may not want to. Something as simple as this can just be done in the DSV. Then it doesn’t matter whether the ETL has run or not, the cube will always be correct (so long as it has been processed).
Regards
Alex
Fantastic, thank you very much. Are there any plans for SQL Relay sessions this year (I have just realised I missed the SQL Bits earlier this month).
Mark
Absolutely, we’re planning 8 events around the UK from 5th to 15th October. No further details yet I’m afraid, but watch this space!
The SQL Bits videos should also be coming on-line shortly, there were some great sessions so keep an eye out on the Bits website.
Hi Alex,
Sorry to disturb you again, but it turns out that the calculation I described above is crushing the performance of the cube (when viewed in excel, but not when published as a web page). Could I move this to the DSV as well?
Or, can you think of anything that I can do to try to optimise the calculation?
Regards Mark
Hi Mark
You’d struggle to do it in the DSV as you use Time.CurrentMember, so the MDX is aware of which time hierarchy level is being looked at. You don’t have that context awareness in the DSV.
Regarding writing more efficient MDX, with something like this it would be necessary to have a copy of the processed cube in order to understand the situation in much more detail – so very difficult do do via a blog post I’m afraid!
Regards
Alex
Hi Alex,
I am new to MDX and tried various MDX query to handle this requirement but couldnt get output as expected. Below is my table structure.
Policyno Startdate Enddate
56486 20150110 20150510
78945 20141219 20150224
I want to calculate policy backlog’s between startdate and end date .Below is the expected output
YearWise blog
2014 1(78945)
2015 0
MonthWise blog
2014 Dec 1 (78945)
2015 Jan 2 (56486,78945)
2015 Feb 1
2015 mar 1
2015 apr 1
I want to calculate similar backlog’s based on week, day wise. Do i need to create four separate measures for day, week,month,year wise or single calculated measure is enough to handle this requirement.
Could you please help me to write the calculated member for this or suggest on possible solution to achieve this.
Your help will be much appreciated.
Thanks,
Murugan
Hi Alex,
Can you suggest me:
1. I have two different date hierarchies.
2. Both have different attribute relationships.
I need to display Last Non Empty Value of a member. I cannot use Last Non Empty because my measure column is Distinct Count.
I have a added a calculation as follows, which works for one hierarchy, I need to modify this to support other hierarchy which is [Date].[Fiscal Year Month].
sum(
generate(
Descendants([Date].[Fiscal Date].currentmember, [Date].[Fiscal Date])
,tail(
nonempty(Existing [Date].[Fiscal Date].[Date Desc], [Measures].[A])
,1)
)
,[Measures].[A])
Hi Alex,
First, thank you, thank you, thank you for this blog and thread!
I’ve been pulling my hair out trying to get a Multi-Date Dim Calculation to work out.
I need a little help. I can’t get the most basic proof of concept working of what you have above.
Very frustrating, since I am now so close thanks to your great thread.
The MDX snippet you give me gets me the dates,
but w/ “#error” for the calculation results.
For instance:
CREATE MEMBER [MyCube].[Measures].[MyFoo] AS
AGGREGATE(
{ [Service Start Date].[by Month Hierarchy].[Month].CurrentMember }
* { [Service End Date].[by Month Hierarchy].[Month].CurrentMember }
,
[Measures].[MyValue]
) ;
SELECT
[MyFoo] ON 0,
[Service Start Date].[by Month Hierarchy].members ON 1
FROM [MyCube]
Is there some kind of dim design property
I should check that could be causing my #error (other than defaulting to All)?
My ultimate goal is:
-A query solution for what really should of been set up at a lower design level.
-A financial ratio where:
-The numerator is the measure over a last rolling 12 months of the a date dim.
-The denominator is the measure over a previous 12 rolling months of a different date dim.
I can get either numerator or denominator working in MDX, but not both in the same query.
Hi Doug
I’m not sure I understand the problem – Why do you need to use the AGGREGATE(xxx * xxx) in the first place? In my example it’s necessary as we want to create a set from null:CurrentMember from multiple hierarchies. However if you’re not doing that, and are just filtering by one hierarchy member or another then can’t you just use the [Measures].[MyValue] measure directly without a calculation? Maybe I’m misunderstanding the problem…
Either way, if you can get calculations for the numerator and denominator working separately then just create two calculated measures, one for each, then a third to do the calculation of one divided by the other. Then just hide the first two.
Regards
Alex
Hi Alex,
Thank you so much for your reply and insights..
Wish I could buy you a pint.
My bad. I had muddied the waters with two distinct issues.
Issue #1 is not getting a basic example to work from above (as my mini Proof of Concept).
Issue #2 is the broader problem, I am trying to solve.
I had figured if I could get #1 working, then #2 would flow naturally.
For #1) I just worked off the wrong snippet. I had been working with the MDX snippet w/ and without the “null:” component in the set declarations.. In both bases, it just gives me an #Error for the calculation. I should of included the snipped above w/ Null: to avoid any confusions. My apologies. I figured I was missing some else, probably obvious, instead.
For #2) The bigger issue. I was hoping to spare you this logic ugliness. Essentially your approach of splitting this up into multiple calculated measure IS the current approach. Four measures in fact. The problem is it’s not completely working for me, so I am trying to get simpler examples working first and build up from there.
I have a current solution that looks like this:
RootCalcMember1: Measure Count over [Product Dim] – Count of Product A
RootCalcMember2: Measure Count over [Product Dim] – Count of Product B
TimeCalcMember1: Sum of RootCalcMember1
over: [Start Date] ParallelPeriod (12 months ago)
to: [Start Date] ParallelPeriod (Current month)
TimeCalcMember2: Sum of RootCalcMember2
over: [Start Date] ParallelPeriod (13 months ago)
to: [Start Date] ParallelPeriod (24 months ago)
RatioCalcMeasure: TimeCalcMember1/ TimeCalcMember2
The above works and predates my arrival.
My task is to try and create an alt version that will
now span two different date dimensions:
RootCalcMember1: Measure Count over [Product Dim] – Count of Product A
RootCalcMember2: Measure Count over [Product Dim] – Count of Product B
TimeCalcMember1: Sum of RootCalcMember1
over: [START Date] ParallePeriod (12 months ago)
to: [START Date] ParallePeriod (Current month)
TimeCalcMember2: Sum of RootCalcMember2
over: [END Date] ParallelPeriod (13 months ago)
to: [END Date] ParallePeriod (24 months ago)
RatioCalcMeasure: TimeCalcMember1/ TimeCalcMember2
In trying various iterations of this logic I find I can get a result when I only include one date dimension in the layers of calculated members. It’s why the basic idea of aggregating the date dimensions first, expressed in this thread seems like the way to go. However, if I can’t get the foundation level working, I am a bit in trouble? 😉
Thanks…
Ok, Well, I just got the my #1 basic example to work, by playing with syntax.
Doh! Blame it on re-learning MDX syntax again after almost 10 years away from it.
And on last little bit of info:
— This now works for me as Basic PoC (Works)–
WITH MEMBER [Measures].[Test]
AS
AGGREGATE(
{ Null:[Service Date].[by Month Hierarchy].CurrentMember }
* { Null:[Service End Date].[by Month Hierarchy].CurrentMember }
,
[Measures].[Count]
)
SELECT [Test] oN COLUMNS ,
[Service Date].[by Month].[Month] ON ROWS
FROM [MyCube]
— But adding an extra complication makes it all fall apart with an #error–
WITH
MEMBER RootMeasuredCalc AS
SUM(
[Measures].[Count],
{[Product].[IsProductType].&[Renewal]}
)
MEMBER [Measures].[Test]
AS
AGGREGATE(
{ Null:[Service Date].[by Month Hierarchy].CurrentMember }
* { Null:[Service End Date].[by Month Hierarchy].CurrentMember }
,
RootMeasuredCalc
)
SELECT
[Test] ON COLUMNS ,
[Service Date].[by Month].[Month] ON ROWS
FROM [MyCube]
Hi Doug
I think this is the wrong approach for what you’re looking to achieve.
This method of cross joining two date hierarchies is for applications where a single calculation needs to honour either one date hierarchy selection, or another, but both applying to the same calculation.
In your example, you want two different calculations to each use a different date hierarchy. So there’s no benefit or point in cross joining the two dimensions. They need to be kept separate.
What you actually need to do is:
– Start out with the principle that it is [Service Date] that is acting as the definition of what is the ‘current date’, this is the dimension on the rows, and essentially is the starting point for each calculation.
– TimeCalcMember1 then wants to find data, where [Start Date] is within 12 months of the relevant [Service Date]
– TimeCalcMember2 then wants to find data, where [End Date] is within 24-12 months of the relevant [Service Date]
So the question becomes, how to we take the current member of the [Service Date] dimension and apply it to the [Start Date] or [End Date] dimensions.
You do this by using the LINKMEMBER function.
http://mdxpert.com/Functions/MDXFunction.aspx?f=33
Something like this (freehand MDX, so please excuse any errors!):
MEMBER [Measures].[TimeCalcMember1]
AS
SUM({PARALLELPERIOD([Service Start Date].[by Month Hierarchy].[Year], 1, LINKMEMBER([Service Date].[by Month Hierarchy].CURRENTMEMBER, [Service Start Date].[by Month Hierarchy])) : LINKMEMBER([Service Date].[by Month Hierarchy].CURRENTMEMBER, [Service Start Date].[by Month Hierarchy])}
,
RootCalcMember1
)
Hope this helps
Alex
Hi Alex, thank you many times over for your reply.
But sadly, I am not sure this suggestion will work.
I may be totally missing something, so beg your forgiveness, if I don’t see it.
I don’t have one service date that needs to be attached to either a start or end date dimension.
The fact record contains both a start and an end date.Behind the scenes there is a separate dimension for each of the keys, but the same underlying date (a role playing dim?).. I don’t really have a single central key to link them both up that I can define a date range against.
In which case, how are you defining ‘Today’ from a report perspective? If you need to go back 12 and 24 months, you need to be able to provide the user with a date selection that defines the report date, from which other date based calculations can then work from.
You can’t let the user filter both the Start and End date hierarchies, as they will interfere with each other. So I’d suggest that you need to add a common Date dimension for this purpose. It doesn’t have to be related to the fact table, it’s just used to provide a reporting date selection.
Hi Alex, Thank you again!
I think you might be on to something, what I need to try and work out is if the start date is also the proverbial “today date” for my purposes..
To answer you question (and this starts to get even deeper into the business need I was trying to spare you from). Suffice to say, there really is no independent “today” concept on the fact now. There is a Service Start & a Service End to a particular fact. Really, the only relationship, is that the end is further on than the start. Now, in the past, there was a “today” ..it was the start date and they just dynamically determined the end date based on a number of other factors, but that won’t meet needs any longer, hence the adding of stored end date (which can be any number of days out) , and also the need to add a new financial reporting ratio to go with it. The interface is an open ended pivot where it is known only certain combinations will produce a result.
I was imagining “today” as relative based on weather your looking at the start…or looking at the end date. In each case, it’s looking back over previous month ranges of that particular date to get their mutually independent numerators and dominator values. This was all fine and dandy until multiple date dimensions got involved and had to be honored.
I only have a couple of more rounds left in me, before I toss in the white towel and declare this not possible and try to run two distinct mdx queries in Excel and combine the results there…
I just wanted to close this out by saying I think I did find a solution that will work for the moment.
I ended up constructing a semi-additive separate measure group and based my calculated members off of that.
The fact table for the new measure group is a union of two data sets out of the original fact table. One for the numerator member and one for the denominator member. Each of the these two members is in their own field and is a distinct measure so when rolling up in the cube they don’t interfere with each other. To get around the date combination technical issue, they both use a single date dimension. In the case of the numerator the single date dimension is based on the original Fact’s start date, but aliased. In the case of the denominator member it’s based off the original end date but aliased as the same name as the start date.
So far my calculations are working and tie-ing out.
It’s a hack, but good enough for the moment.
Dear Alex,
How are you? I hope fine! 🙂
I need you help… can I?
I’ve followed you suggestion some time ago, but now I need to make some improvements on performance…
The problem is when I use a Document Dimension that has 400.000 records…
The MDX try to create the stock level for each document even if no data is present for the document.
DO you know how can I avoid this?
Thank you !!
Hey Pedro
It sounds like you’re using MDX to solve a problem that may be better being solved in the data modelling/ETL layer. I’d re-assess what you’re trying to calculate, and determine what the optimal data model should be.
Have a look at the following video for tips on how to re-think your cube data model
http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes
Regards
Alex
I started to tried to add to ETL the stock snapshot… the amount of records pass from 2 million (just movements) to 200 milions…
The solution are in production working good! Just a problem, when add a Document Dimension…
Cheers and thanks!
Hi Alex,
You have a great blog. It is the very first time I am writing a question in anyone’s blog. My question is also related to SSAS and MDX query. Since Cubes keep the history our client is looking for a count of measures and keys in a specific time. E.g. They would not only like to the the count of different account statuses as of today but what was the count on March 2nd, or what was the count between year 2012 and 2013. I am not sure how to achieve this. Do I use “tail” or how can I create a calculation or a member that anytime it will be dragged and dropped by customers and whatever month, day, year they choose it will show the count for that period of time.
I appreciate your time and effort to look into this for me.
Thanks a lot in advance and hope to hear from you soon.
Best Regards,
Rim
Hi Rim
To display a count of measures you need to add a new measure to the measure group, with the aggregate function of “Count”. This will count the number of records in the underlying fact table. This will automatically work with any date range selected. The date selection filters the measure group, and so the record count measure decreases. If you want more control, then you can add a new column to the underlying fact table/view, with a fixed value of 1. Then you can just “sum” this new field. The result will be the same, however you can then add logic into the source table/view to only have a value of 1 if the record meets certain criteria (e.g. Value>0 or something).
Regards
Alex
Hi Alex,
I am stuck in a situation where I want to use YTD for three different calendars of our company and don’t want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it doesnot give my right results. Following is my syntax but It does not work. Please advise if it’s possible or not.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
END SCOPE;
SCOPE([Billing Date].[FinancialCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[FinancialCalendar].[Financial Year],[Billing Date].[FinancialCalendar].CURRENTMEMBER) } );
END SCOPE;
However if I comment on one of SCOPE statement , other one works but both don’t work simultaneously in context of different calendars.
Any gudiance will be helpful.
Regards,
I want to calculate closing inventory and I have 1 date dimension having calendar and fiscal dates
I have 4 hierarchies in my date dimension:
Fiscal (which is Fiscal Year / Quarter / Period and Date)
Fiscal – Period (Fiscal Year / Period)
Calendar (Year / Quarter / Month / Date)
Calendar Year – Month (Year / Month)
This work fine if I want to do something like this:
Fiscal Year – Period Sales
2014 500,000
P1 200,000
P2 100,000
P3 200,000
2015 700,000
P1 300,000
P2 200,000
P3 200,000
But I want to display Fiscal Year on rows and Periods on columns, so I also made available Fiscal Year and Period so I can have this:
Fiscal Year P1 P2 P3 Total
2014 200,000 100,000 200,000 500,000
2015 300,000 200,000 200,000 700,000
This works fine when using values from the cube.
But I wanted to calculate Closing Inventory, so here is the code:
AGGREGATE(
{null:[Date].[Calendar].CurrentMember}
* {null:[Date].[Calendar Year – Month].CurrentMember}
* {null:[Date].[Fiscal].CurrentMember}
* {null:[Date].[Fiscal Year – Period].CurrentMember}
* {null:[Date].[Date].CurrentMember}
, [Measures].[Unit])
For my hierarchies (Calendar, Calendar Year – Month, Fiscal, Fiscal Year – Period) it works perfectly.
But since I wanted to have fiscal year on rows and periods on columns, I added this to the code:
AGGREGATE(
{null:[Date].[Calendar].CurrentMember}
* {null:[Date].[Calendar Year – Month].CurrentMember}
* {null:[Date].[Fiscal].CurrentMember}
* {null:[Date].[Fiscal Year – Period].CurrentMember}
* {null:[Date].[Date].CurrentMember}
* {null:[Date].[Fiscal Year].CurrentMember}
* {null:[Date].[Period].CurrentMember}
, [Measures].[Unit])
After this change, hierarchies were still giving the same number but when using Fiscal Year (rows) and Period (columns) I was getting different numbers.
How can I have this closing inventory calculation work ?
BTW, in the hierarchy called “Fiscal Year – Period”, the key for the Period is a combination of [Fiscal Year] and [Period], making it unique but I can’t use this same key when setting up [Period] because when I put [Period] on columns, it displays periods separately among fiscal years:
P1 P2 P3 P1 P2 P3
2014 200,000 100,000 200,000
2015 300,000 200,000 200,000
thank you
I found the cause of my problems and it works now !
My hierarchy [Fiscal Year – Period] was using [Period Full]. It’s key was [Fiscal Year + Period] but in fact it has to be [Period].
I need one MDX script
Below is the relational query
Select COUNT(distinct empID) from Emp
Where EmpType >10
I have Employee Dimension with attribute empID ,EmpType
I created distinct count (Employee.EmpID.EmpID) as Measure
How can i get the distinct empID count using EmpType in MDX
It should be as simple as:
SELECT [Measures].[EmpID Distinct Count] ON 0
FROM (SELECT {[Employee].[EmpType].[11]:null} ON 0 FROM [Cube Name])
Alex
Hi Alex,
I was making new enhancements to SSAS cube and deployed changes on UAT and cube was working properly. After few months, user sent UAT feedback with few issues but when re-processed the cube without making any changes at all in assumption that issues would be fixed.
In the cube designer when I check ‘Browser’ tab and drag measures, no data is seen. i can find new expected data in dimensions and no data in measures. It is basically empty.
I would be okay if data is not there for expected month/date data as per my changes but old existing data(ex,2011-2014) should be impacted but nothing is appearing. if i click on DSV or cube structure data explore shows data.
I’ve re-process cube many times, imported latest production cube in BIDS2008 and deploy, processed but finally when cube process complete i try to browse and just dragged amount measure, its EMPTY.
One thing i noticed, this is happened to 2 measure group which have parition, rest all measure group are okay.
Would you please shade some lights on how to fix this problem.
Looking forward to get some quick resolution tips.
Thanks,
Niel
one more things Alex..
This cube has been used Unary Operators in Parent-Child Dimensions / roll up.
if i get production copy, it should work without any issue in development box but after processing measure are empty
Thanks,
Nilesh
Hi Niel
It sounds like the source queries for the partitions are incorrect, and therefore not bringing in the data.
Look at the properties of one of the partitions, copy the source query, and run it in SSMS. Does it return the required data?
Alex
Thanks Alex.
Yes, I checked the source queries of all partition and those are retreiving expected rows when run in SSMS.
Yesterday, what I did. I restored OLTP production backup until 31st July 2015 and restored current OLAP cube from production as well.
when I open the cube in BIDS and browse, I was able to see data in expected measures.
However, when I pointed data source to development database and process cube full and again browse, data disappeared from measures.
One thing, I noticed that data is empty for only those measure groups which having partition otherwise non partition measure groups are fine(I can see data when I drag any measure from those groups)
I tried changing processing setting because in Dev it was parrellel but in production cube process through SSIS package where it has been set sequential but it didn’t work either.
Also, I tried to point production OLTP and re-processed cube but it result is same.
Do you think it could be SQL server 2008R2 issue or bids2008R2? I checked both database version and those are exact same.
Looking forward to hear from you.
Thanks,
Nilesh
Hi Alex, just to add up…..
Last night I kicked off Cube from BIDS pointing to production OLTP and now I checked browsed cube and all measures are showing data.
It means problem with development data or whatever data I have restored until 31st July 2015 in development from productions was broken relationship……… I think, if I restored up to date data from prod , it should work
Let me know your thoughts ..
Thanks,
Niel
The only thing that makes sense is either
– different source data in both databases
– incorrect connection string settings (pointing to wrong server/database)
– problems with the partition queries (not the measure group source, but the individual partition source)
If you’ve been through all of those and none are an issue, then I’d put a profile trace on the source database whilst you process one of the problematic partitions, as well as adding PerfMon counters on ‘Processing/Total rows read’ and ‘Processing/Total rows written’. Between them they should shed some light on what’s going on, or at least point you in the right direction.
Hi Alex,
i’m so new in MDX and this post has been helpfull in someways although my issue is not even the same as the topic of the post, but i’m sure you can help me.
i need to calculate de percentage of items that a brand has in relation to the total percentage of items of the category to which it belongs, something like this in SQL:
select p.idCategory, p.idBrand, (sum(p.num_items)/ Cat_num_items)*100
from fact_table p
group by p.idCategory, p.idBrand;
CAT | BRAND | %
————————-
A|X|49
A|Y|51
B|Z|80
B|W|1
B|S |19
C|Z|75
C|S |24
C|T|1
I have 3 dimesions Category, Brand and Period, and [Measures].[Brand_SumItems] aggregating a sum of num_itemsOfBrand field and [Measures].[Category_SumItems] aggregating a sum of num_itemsOfCategory field and finally a Calculated member with formula: [Measures].[Brand_SumItems].Value/[Measures].[Category_SumItems].Value)* 100
This does not work because i loose the relation between brand and the category that it belongs, making a global calculation with total number of items by brand and total number of items by category separatly (gives results over 100%)
The thing is that in the fact table i already have a field num_itemsOfCategory, that has the total number of items for the category of the brand selected so i don’t need to do a sum in this [Measures].[Category_SumItems], i just need the value in the formula but i don’t know how to do that so the calculated member should look something like [Measures].[Brand_SumItems].Value/Cat_num_items_FieldValue)* 100
I’m sure i probably have design problems with my fact table or also in mdx cube modeling and i can change them in order to make them work, please help me.
thank you in advance and please forgive if this is such a basic cuestion but i’m very new on this..
PS: after that i have to cross it with periods of time (year,month,past 6months, past 12months) and make a top ten values sorted desc. But i’ll think about this later, first i need to get the percentage calculation so i can cross it later. please give me some guide..
Hi Yoanna
The easiest solution to this is to fix the data model. If there is a hierarchy relationship between Brand and Category then they should be in the same dimension, with a hierarchy between them.
Then you can simply use [Measures].[x] / ([Measures].[x], [Brand].[Brand Hierarchy].CurrentMember.Parent)
If your data model is not suitable then you’ll end up in a world of pain, take every effort to get the model right instead of battling with getting MDX to try and fix the problem – you’ll end up fixing the data model at some point anyway.
Regards
Alex
Hi Alex! thanks for your quick response.. I tried what you suggested and it made a lot of sense, it gave me more understanding of what i’m looking for, but i realize that my issue now is not in the number of items by category but in the number of items by brand because the relation between them is “many to many”, a brand could be related to more than 1 category as in the example that i posted (brand=Z). So the measure where I get the number of items [Measures].[x] by brand is not considering that, just the global sum of items by brand without taking in account the category to which it belongs.
In the fact table now i have: CAT| BRAND | Num_Items, and i need to do this:
CAT| BRAND | Num_Items | Formula | PERCENTAGE
B | Z | 800 | (800/(800+200))*100 | 80
B | S | 200 | (200/(800+200))*100 | 20
C | Z | 750 | (750/(750+250))*100 | 75
C | S | 250 | (250/(750+250))*100 | 25
Right now using what you suggested (that was very helpfull) i get this result (800+750/(800+200)*100 = 155%. How could i fix this? thank you so much in advance!
I forgot to tell you, to get that result i changed the dimensions, i got just one dimension with 2 hierarchies (Category and Brand) and the measure you suggested.. I was thinking maybe changing the order of the hierarchies making Brand the parent of Category and arranging the calculated member as something like:
([Measures].[x], [Dimension].[Category Hierarchy].CurrentMember.Parent) / ([Measures].[x])
since the currentMember is the category, but is not working so far, i’m totally missing something, i’ll keep researching .. appreciate any other tips you may have.. thx 🙂
Hi, I have to admit that I dit not read all of this post. I am quite new to MDX but I want to do exactly what you describe in the example at the top and if I do it as you describe, it works.
What I do not understand is why this does not work. I would have expected that by working with (All) it would work for all hierarchies in the date dimension, but it doesn’t. It only works with the Calender Year Month and not with the Fiscal Year Month…
WITH MEMBER [Measures].[Sold To Date] AS ‘Sum(PeriodsToDate([Date].[(All)]),[Measures].[Sales Qty])’
SELECT
{[Measures].[Sales Qty],[Measures].[Sold To Date]} ON COLUMNS,
[Date].[Calendar Year Month].Members ON ROWS
FROM [MyCube]
Hi Alex,
I have been working on a solution proposed by Sal. Followings is the article I am referring to:
https://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/
I have tried to reach Sal but could not get hold of him. Maybe you can help please.
In his example Sal is using a separate table to dynamically calculate YTD values of all the measure that are included in the query.
In the example he has used [Date Calculations].[Date Calculations].[Selected Date] but he has not set it’s value anywhere.
Qs 1:
Please help me understand what is the purpose of putting [Date Calculations].[Date Calculations].[Selected Date] and how it is getting the value of the current measure automatically.
[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(
{[Date Calculations].[Date Calculations].[Selected Date]} *
PERIODSTODATE
(
[Date].[Calendar Hierarchy].[Calendar Year],
[Date].[Calendar Hierarchy].CURRENTMEMBER
)
);
Qs 2:
Where is [Date Calculations].[Date Calculations].[Selected Date] getting it’s value?
Qs 3:
What do I have to do to hide the [Selected Date] from showing in the result?
Once again Alex, thank you in advance for helping me understand the tips and tricks of MDX scripts. Your articles and sharing have been very helpful to me as I learn the SSAS ways of data analysis.
Thanks
Joy
Sorry forgot to check the notify me checkbox… 🙂
Hi Joy
The purpose of this Date calculation dimension is to allow you to easily select date calculations by selecting a member of this dimension. There does however need to be a base member, on which no calculations are applied. This shows the number without any date calculations, i.e. as if this dimension didn’t exist. This is the [Selected Date] member.
Other calculations are then applied on top of the [Selected Date] member.
[Date Calculations].[Date Calculations].[Selected Date] should be set to be the default member of the [Date Calculations].[Date Calculations] attribute, to save you from having to select it manually every time.
Once you set it as the default member, you no longer need to select it in your pivots, and so it will not show the title. It will only show once you add it into your pivot, at which point you need it in order to differentiate the values from YTD etc.
Hope this helps
Alex
Hi Alex
I’m Using Funcation Like Aggregate({NULL:[Date….CurrentMember]},…Count)
But When I drill down in each Dimention its so Slow,
The performance issues you experience won’t be caused by this MDX approach, but by an underlying problem in the cube.
Cube performance issues are usually caused by:
– Poor design
– Poor partitioning
– Missing attribute relationships
– Poor aggregation design
– Insufficient hardware
There’s no way to offer any more detail without seeing your cube implementation, but if you review all of the above then you’ll hopefully find the problem.
Thanks
Alex
Thanks Alex,
i have a cube contain 4 Dimensions:
1. Equipment
2. Customer
3. Dates
4. Location
i’d like to get the total number of equipment on Date hierarchy and drill down on Equipment Type, Customer Type, Location Type
i use 1 for active and -1 for deactive Equipment in ETL Process and have a one measure to SUM this,
i use above aggregation , when just browse on Date hierarchy , it’s good, but when i add other dimension, it’s so slow.
i have no partition and no aggregations.
“i have no partition and no aggregations.”
This will likely be the cause of your performance problems. SSAS needs partitions and aggregations to perform well.
yeah, your right , but in this way, i would like to calculate from start to Now, how can i partition it ? in date? no difference because it calculate total number on all of partition .
Yes, partition by date, week, month – whatever is most appropriate for your data.
Even though you’re accessing all dates, it will make a difference.
There may also be design problems in your cube which are impacting performance, but I can’t comment on that without seeing your code.
Thanks Alex, I try to solve my problem due to your advice…
Alex, this is a remarkable thread, I admire your continued interaction with everyone.
I’m trying to implement a Date Tool dimension to allow for aggregating various measures in my cube across several date dimensions. I have studied this thread and keep hitting the wall–can you offer some help?
When deployed, this calculation only successfully applies to the Production Date dimension in my Excel pivot table. If I swap the order and put Production Date first and then Service Date, then Service Date will work.
SCOPE ([Date Tool].[Calc].[Year To Date]);
THIS = AGGREGATE (
YTD ([Service Date].[Service Date Hierarchy].CurrentMember)
, [Date Tool].[Calc].DefaultMember
);
THIS = AGGREGATE (
YTD ([Production Date].[Production Date Hierarchy].CurrentMember)
, [Date Tool].[Calc].DefaultMember
);
END SCOPE;
My problem seems very similar to this comment: https://purplefrogsystems.com/2010/08/mdx-calculated-member-spanning-multiple-date-dimensions/#comment-1593
I tried to fit your solution into my code but nothing worked. Any assistance appreciated.
The problem is that both of the statements will always execute, so the second one will always override the first.
You need to find a way of enhancing the SCOPE to narrow things down so that the relevant calculations only execute when you want them to.
You could try something like this:
SCOPE ([Date Tool].[Calc].[Year To Date]);
THIS = AGGREGATE (
YTD ([Service Date].[Service Date Hierarchy].CurrentMember)
, [Date Tool].[Calc].DefaultMember
);
END SCOPE;
SCOPE ([Date Tool].[Calc].[Year To Date], [Service Date].[Service Date Hierarchy].[All]);
THIS = AGGREGATE (
YTD ([Production Date].[Production Date Hierarchy].CurrentMember)
, [Date Tool].[Calc].DefaultMember
);
END SCOPE;
This starts by using the Service Date dimension, but if the [All] member of the service date dimension is selected, then it will switch to using the Production Date calculation instead.
It works as you described, thank you very much for the input.
Alex, it turns out there are twelve different date hierarchies in the cube that will need to work with my Date Tool. Am I facing the task of creating SCOPE statements for each permutation of ALL members across the twelve date dimensions, or do you see a more elegant approach?
Yes you would need to do exactly that, but first decide the priority order or logic of the dimensions. I.e. If a user selects from two date dimensions what should happen? Just use the highest priority dimension? Or try and use both? This will impact how you approach the code.
Wow, this is quite a long thread. I tried to browse through the thread to see if my question has been answered but I am not sure. Here is my question:
I I want to span multiple MEMBERS and not hierarchies it seems that the MDX is limited since it is doing CurrentMember. When I select multiple members then my count ends up being -1. Is this a limitation on the MDX? Is there a workaround to make work with multiple members selected like Jan, Feb and Mar for the same date hierarchy?
Thanks in advance.
You’re right, the CURRENTMEMBER solution will only work when there is a single member selected, it does not support a multi-select.
For that you’d have to come up with another solution, and it will get more complicated. You can work with multi-selects inside MDX calculations using the EXISTING function, so as a first thought maybe look at using FILTER with EXISTING to see if you can build the set of members you need.
Thanks for the confirmation. By the way, this is quite a data modeling technique!
Hi, Alex. I’ve seen all your comments and my word “you are cool master” 🙂
i just want to ask you recommend me usefull booj or maybe video lessons about MDX language to understand differents between hierarhy and current member, what functions works with hierarchy and currentMember? descadans, tail, ytd, closeperiod etc.
i just want to figure out 🙂
thank you, body.
Roman, Ukraine Kyiv.
Alex, I have literally NEVER in my life read an entire blog , perhaps an entire web page for tech help. This long ass crazy in depth tech support is insane! I seriously cant believe how much you willing help people for free. You are a good guy with a lot to offer, thanks dude.
I actually came here cuz I jumped into mdx like “how hard could it be” since ive been in sql for-ever. Shocked at my hurdles when I just want to show stupid months across the columns and years in the rows for Year-to-date calc. I had no idea this wasnt an out of the box function. So I have copied a ton of snips that look like what im dealing with and i am sure it will help, so thx again
Hi Alli
Thanks for your kind words – glad the post is of use, good luck with your MDX!
Alex
Hi alex, after reading your post I haveimplement a scenario in mdx
find out the number of complaint in the reporting month like
select count(*) from t where complint_month_year = 08215 and resolution_month_year >08215
WITH MEMBER MEASURES.[PENDING_AT_BEG]
AS
AGGREGATE(
(
{
LINKMEMBER([Dim Date].[WEEK_Hierarchy].currentmember,[COMPLAINT_DATE].[WEEK_Hierarchy]
)
}
* {
LINKMEMBER([Dim Date].[WEEK_Hierarchy].CURRENTMEMBER.nextmember, [RESOLUTION_DATE].[WEEK_Hierarchy]
)
:NULL}
)
,[Measures].[COUNT_COMPLAINTS]
)
SELECT { MEASURES.[PENDING_AT_BEG]} ON 0,
NON EMPTY{([Dim Date].[WEEK_Hierarchy].[Month Name] ) } ON 1 FROM
(SELECT (STRTOMEMBER(@YEAR, CONSTRAINED)) ON COLUMNS FROM
(SELECT (STRTOMEMBER(@MONTH, CONSTRAINED)) ON COLUMNS
FROM [CUBE_xxx]))
but it showing null all time
Hi,
I have a performance issue on this mdx:
with MEMBER [e]
AS
AGGREGATE({NULL:LINKMEMBER([Record Date].[Date Hierarchy].currentmember
,[START Date].[Date Hierarchy])}*
{LINKMEMBER([Record Date].[Date Hierarchy].currentmember
, [END Date].[Date Hierarchy]):NULL}
, [Measures].[Count of Preference])
SELECT {[e]} on 0
from [Cube]
where ([Record Date].[Date Hierarchy].[Day].&[2016-01-11T00:00:00])
This takes 11 mins which is not acceptable. any idea why it is so slow.
Hi Alex,
I am stuck in a very similar situation for rolling periods member of period utility dimension which I want to evaluate over two hieraechies, fiscal and calendar (for day, month and quarter level for both hiearchies). Issue is only one of them is being evluated at one time.
Here is sample code
Scope([Period].[Period].[Rolling 12 months]);
Scope([Date].[Calendar].[Calendar Month]);
([Period].[Period].[Rolling 12 months])= Aggregate([Period].[Period].&[0] * LASTPERIODS( 12,[Date].[Calendar].CurrentMember));
End Scope;
Scope([Date].[Fiscal].[Fiscal Period]);
([Period].[Period].[Rolling 12 months])= Aggregate([Period].[Period].&[0] * LASTPERIODS( 12,[Date].[Fiscal].CurrentMember));
End Scope;
End Scope;
Similar code applies for day and quarter as well for both hierarchies, however only one of them runs at one time, I believe we need to set default behaviour and scope the other one.
Please advise.
Yes you’re right, you need to set the default code to work with one of the hierarchies, and then scope the calculation to use the other when the first has ‘all’ selected.
So you could have the default code using [Date].[Calendar] and then scope the exception for [Date].[Calendar].[all] to use [Date].[Fiscal] instead.
Regards
Alex
Hi Alex,
I have a problem with data displaying and calculation matter. The following MDX displays all the positive total (MEASURES.Total) and if any negative total then converted them into + value and are displayed accordingly. However, my requirement is in Hierarchy Marketing i have Expense and Income, i want to display all expenses and those income have negative value and exclude positive income from calculation. So the TOTAL value will be all expenses+ (-) income per Traders. But currently it is being calculated as separately i mean, for example trader (barlen) shows 2 total 1 for expense and another for income of marketing.
could you please find that solution ? I would be really appreciate your help, because last 2 weeks i have been trying to sort out this problem.
WITH
MEMBER [Measures].[Negative Total] AS
CASE
WHEN ([Measures].[Total]) < 0 THEN [Measures].[Total]* -1
ELSE [Measures].[Total]
END
MEMBER
[Dim Account].[Account Activity].TOTAL AS
[Dim Account].[Account Activity].[All]
//SUM(Traders,[Measures].[Total])
MEMBER [Dim Branch].[Trading As].[Pedders Suspension]
AS
Aggregate
(
{
[Dim Branch].[Trading As].&[PED]&[Pedders Suspension]
,
[Dim Branch].[Trading As].&[PHO]&[Pedders Hornsby]
}
)
SET Traders AS
{
[Dim Branch].[Trading As].&[MEC]&[Autoco Mechanical]
,
[Dim Branch].[Trading As].&[RSA]&[Roadside Assist]
,
[Dim Branch].[Trading As].&[BSE]&[B Select]
,
[Dim Branch].[Trading As].[Pedders Suspension]
,
[Dim Branch].[Trading As].&[ASR]&[Autoco Smash Repair]
,
[Dim Branch].[Trading As].&[BAR]&[Barlens Event Hire]
}
SELECT
NON EMPTY
{
[Measures].[Negative Total], [Measures].[Total_Income_Percent]
}
ON COLUMNS
,
NON EMPTY
(
{
(
[Dim Account].[HierarchyMarketing].[Account Type].&[Income]
*
{
VisualTotals({[Dim Account].[Account Activity].[All]})
}
)
,
{
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
,
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
}
*
[Dim Account].[Account Activity].[Account Activity].MEMBERS
,
{
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
,
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
}
*
[Dim Account].[Account Activity].TOTAL
}
,
[Dim Fiscal Year].[HierarchyFiscal].[E Month].&[2016]&[December]
, NONEMPTY([Dim Scenario].[Scenario Name].&[Actual])
,
Traders
)
ON ROWS
FROM [CubeProfitLoss]
Hi,
I’m using Tail function to get the date of last non empty value, But this gives poor performance and the report is taking more than 3 minutes to display the result. Please find the below query.. Can you suggest an alternative solution for this?
member [measures].[lastused] as Extract(tail(filter({[Sale Date].[Date].children },
not isempty([Measures].[Sales Item Count])),
1),
[Sale Date].[Date]).Item(0).Member_caption
Hi Gowri
I’d rewrite the expression as
member [measures].[lastused] as
tail(NONEMPTY([Sale Date].[Date].children, [Measures].[Sales Item Count]),1).item(0).Member_caption
i.e. Using NonEmpty instead of Filter. And the extract can also be simplified.
I’m not sure how efficiently SSAS interprets extract & filter compared with the alternative, but hopefully it might improve things for you.
Alex
Hi Alex
I am new to MDX. I have a cube with few dimensions. Report Date and Account are two of them. One account id may come under multiple Report dates. I need to find minimum report date for every accountid. Any ideas? I really do appreciate the help.
Thanks
Anubhav
Hi Anubhav
You need something like this
WITH MEMBER [Measures].[Account Minimum Date] AS
NONEMPTY([Reporting Date].[Date].[Date].MEMBERS, [Measures].[Sales]).item(0).MEMBER_NAME
SELECT [Measures].[Account Minimum Date] ON 0,
NON EMPTY [Account id].[Account id].[Account id].MEMBERS ON 1
FROM [Cube]
i.e. Create the Account Minimum Date as a measure which finds all of the non empty dates, and takes the name of the first item in the resulting set. Note that you need to provide a measure name in the nonempty statement, which lets the cube know which measure you’re using to determine activity, i.e. [measures].[Sales] or something.
Regards
Alex
Hi Alex
Thanks a lot for the help.
i tried the above query
WITH MEMBER [Measures].[Account Minimum Date] AS
NONEMPTY([REPORT DATE].[Report Date_1].MEMBERS, [Measures].[# Units]).item(0).MEMBER_NAME
SELECT [Measures].[Account Minimum Date] ON 0,
NON EMPTY [CONTRACT].[Agreement ID].members ON 1
FROM [MyCube]
but i am getting “All” for [Account Minimum Date] for every accountid(ie agreementid).
my Report Date_1 is in format “YYYYMMDD”
Any other pointers to get the date instead of “All” values?
This is because [REPORT DATE].[Report Date_1].MEMBERS includes all members of the hierarchy, ALL and everything below it.
If you replace it with [REPORT DATE].[Report Date_1].[Report Date_1].MEMBERS then it skips past the hierarchy and returns just the actual members.
So change your query to
WITH MEMBER [Measures].[Account Minimum Date] AS
NONEMPTY([REPORT DATE].[Report Date_1].[Report Date_1].MEMBERS, [Measures].[# Units]).item(0).MEMBER_NAME
SELECT [Measures].[Account Minimum Date] ON 0,
NON EMPTY [CONTRACT].[Agreement ID].[Agreement ID].members ON 1
FROM [MyCube]
Thank you Alex, it worked like a charm 🙂
Can you please help me to understand how it is working. Is it the .item(0) that constrains the output to the earliest/minimum REPORT DATE each intersection with Agreement ID. Does this assume an ascending sort order on REPORT DATE? What if i wanted the last/maximum date or had a custom sort?
Hi Anubhav
All attributes in a cube have a specific pre-determined order, the cube relies on this for a lot of functionality. When you’re designing a cube it’s your job to set the ordering of each attribute correctly, whether alphabetically, numerically, by date, or by some other method (i.e. month name is none of the above).
Once you do this, then you can take the first member of the set by using “.item(0)”, or the last member by using “TAIL(xxx, 1).item(0)”
Regards
Alex
Hi Alex,
this page is awesome, very helpfull. But I would like to ask you for help.
My problem is that I would like to calculate measure based on selected date dimension in e.g. Excel. My solution has two date dimension Posting Date and Shipment Date and I would like to calculate time comparison year to date on Deliveried Quantity measure .
Based on previous posts I tried a lot of variations, for example:
SCOPE([Measures].[Deliveried Quantity],[Date].[Date YMD]);
this = AGGREGATE(SUM(YTD([Date].[Date YMD].CurrentMember),
[Measures].[Deliveried Quantity].DefaultMember));
END SCOPE;
SCOPE([Measures].[Deliveried Quantity],[Shipment Date].[Date YMD]);
this = AGGREGATE(SUM(YTD([Shipment Date].[Date YMD].CurrentMember),
[Measures].[Deliveried Quantity].DefaultMember));
END SCOPE;
When I test it in Excel, it produces a #VALUE! error.
Anything you can suggest would be really useful!
Thank you.
Best regards,
Martin
Hi Martin
A couple of things to look at here:
Firstly SUM is a type of AGGREGATE, so you don’t need them both in the same query.
Then there’s a commonly encountered hurdle with SCOPE. No matter what, there is always a member of every attribute selected, whether you have selected one or not. If you haven’t selected one then [All] is selected by default.
So when you write SCOPE([Measures].[Deliveried Quantity],[Shipment Date].[Date YMD]), there will always be a member of [Shipment Date].[Date YMD] selected, so the scoped assignment will always be triggered.
So try replacing it with something like:
SCOPE([Measures].[Deliveried Quantity], DESCENDANTS([Shipment Date].[Date YMD],,AFTER))
Which should exclude the [All] member and make it work.
Regards
Alex
Hello Alex,
Thank you for your support . I need one help from you regarding fiscal year and calendar year.
we have existing Mdx query where initially they are using Fiscal year hierarchy for calculating YTD now they have added one more hierarchy such as calendar year. now they want to browse YTD value on both hierarchy.
i have used scope to display the value which is listed below
scope [Measures].[YTD Actual GWP Sum] ;
scope ([dimTimeDimension].[EGY Calendar Year].members,[dimTimeDimension].[Calendar Year].[Year].members);
this = SUM(
YTD([dimTimeDimension].[Calendar Year].CURRENTMEMBER)
,[Measures].[Gross Written Premium]
);
end scope;
scope ([dimTimeDimension].[Calendar Year].members,[dimTimeDimension].[EGY Calendar Year].[Year].members);
this = SUM(
YTD([dimTimeDimension].[EGY Calendar Year].CURRENTMEMBER)
,[Measures].[Gross Written Premium]
);
end scope;
end scope;
since we have multiple YTD measure so its difficult for us to implement scope in each measure so do we have any other way to implement this one without any huge modification?
Thanks a lot in advance
Hi Alex -I have a simple Year-Period hierarchy and need a rolling figure for the last 52 periods e.g. rolling aggregate figure for Year 15 Period 32 to Year 16 Period 32. I can easily produce an aggregate figure for the full Year-Period hierarchy but need it to reset to zero at start of last 52 periods.
Can you help?
Hi Chris
You just need to identify the last period from whatever level of the date hierarchy is currently selected, and then sum all transactons from that period to lag(11) of that period. That will give you a rolling 12 period window.
The following code does this for the AdventureWorks cube.
WITH MEMBER [Measures].[Rolling 12 Periods] AS
SUM(
{TAIL(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month], SELF),1).Item(0).LAG(11)
:TAIL(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month], SELF),1).Item(0)}
,[Measures].[Internet Sales Amount]
)
SELECT {[Measures].[Internet Sales Amount], [Measures].[Rolling 12 Periods]} ON 0,
NON EMPTY DESCENDANTS([Date].[Calendar], [Date].[Calendar].[Month], SELF_AND_BEFORE) ON 1
FROM [Adventure Works]
Regards
Alex
Hi Alex,
first of all many thanks to this amazing blog – i think you are helping a lot of people! 🙂 I hope you can help me out as well. Here is the case:
I have a fact table which contains among other things a PERIOD_ID (20170930, 20180230, …), ACCOUNT(Assets, Liabilites), and FORECAST INCOME. After the user is setting a so called Report Date (2016-09-30) the user should be able to see 5 Buckets which are summing up the next 12 Months after the Report Date. So when Report Date equals 2016-09-30, the sum of Date 2016-10-31(Reporting Date + 1 month) until 2017-09-30 should be displayed under “Dimension” 1Y. This should go up to 5Y (Sum of Values between 2021-10-31 to 2022-09-30 ).
I hope this is clear and you can give me a hint how i can do this 🙂
Thanks&Best regards
Alex
Hi Alex
I’d use a utility date dimension to achieve this. Create a new dimension with a single attribute containing 5 members (1Y to 5Y). Then use scope to override these member values with a calculation of the 1Y-5Y totals.
The following link is a good walkthrough as a starting point, you’ll just need to change the calculations to select the relevant time period.
https://intelligentsql.wordpress.com/2013/02/15/ssas-date-utility-dimensions-pre-aggregating-over-time/
Alex
Hi,
I tried recreating this formula using ‘*’ in formula for a calculated measure in mondrian cube. I need to pick up the dimension that is used at run time. I tried using * but it is not working in Pentaho mondrian cube. Can you please mention what is the alternative for * in mondrian cube?
Try using the CrossJoin function
Some three years ago when designing our business main OLAP cube we faced this problem, our company analyzes data both monthly and weekly. After weeks of searching dozens of blogs and forums we ended up with a quite complex solution. For example to allow comparation with previous year:
CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
AS iif([Dim Time].[Weeks].CurrentMember IS [Dim Time].[Weeks].[All],
([Measures].[Value], ParallelPeriod([Dim Time].[Months].[Year])),
([Measures].[Value], ParallelPeriod([Dim Time].[Weeks].[ISO Year])))
It works for “almost” all situations.
A couple of days ago I discovered by accident this post and I was delighted! Wow! That’s far more simple and generic, let’s try it.
I have not been able to make it work, neither
CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
as (ParallelPeriod([Dim Time].[Months].[Year] * [Dim Time].[Weeks].[ISO Year])),
[Measures].Value])
nor
CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
as (ParallelPeriod([Dim Time].[Months].[Year]) * ParallelPeriod([Dim Time].[Weeks].[ISO Year])),
[Measures].Value])
works.
Any idea on what are we doing wrong
Any help on this one?
Hi Alex,
I am trying to run one MDX query using Calculation. But it is showing #Error in Column. Could you please help me to correct it?
WITH MEMBER [Measures].[Cal] AS
CASE WHEN [DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].currentmember is [DimOutwardTypes].[OutwardTypeID].&[1] or [DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].currentmember is [DimOutwardTypes].[OutwardTypeID].&[3] THEN
1
ELSE 2
END SELECT NON EMPTY { [Measures].[IAmount], [Measures].[SAmount], [Measures].[CAmount], [Measures].[XAmount], [Measures].[Cal] }
ON COLUMNS, NON EMPTY { ([DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].ALLMEMBERS ) }
ON ROWS FROM [cubeClient]
Thanks,
Hiren
Hi Hiren
Try changing the 2nd line to
CASE WHEN [DimOutwardTypes].[OutwardTypeID].currentmember is…
Regards
Alex
Hi All,
I need to create the ly measures based on whatever date attribute is selected. e.g now it is coupled with the date hierarchy and it only works when i have the date hierarchy in rows or columns. I need it to work for both hierarchy as well as for if the year, e.g if i use the year attribute only it should work as well as for any other date attribute eg. year, month, week etc. Any guesses?
thanks
Roan.
Your best option is to create your LY measure based on the hierarchy. Then add a SCOPE() statement to override the functionality different logic based on each different date attribute that you want to include.
You therefore end up with multiple MDX calculations, each relevant for different date attributes.
Thanks
Alex
hi ,
i am new in SSAS , i have developed a project and facing a problem in below code , i am very inspire by “Alex”.
can any one guide me in my code?
Scope([Measures].[Unit- Closing Stock]);
This = sum(periodstodate(null:[Time].[date].CurrentMember *
null:[Time].[Day Numberof Week].CurrentMember *
null:[Time].[Day Of Month AD].CurrentMember *
null:[Time].[Day Of Month Islamic].CurrentMember *
null:[Time].[Day Of Week].CurrentMember *
null:[Time].[Day Of Year].CurrentMember *
null:[Time].[Islamic Date].CurrentMember *
null:[Time].[Islamic Day Of Month].CurrentMember *
null:[Time].[Islamic Month Name].CurrentMember *
null:[Time].[Islamic Month Number].CurrentMember *
null:[Time].[Quarter].CurrentMember *
null:[Time].[Week Description].CurrentMember *
null:[Time].[Week Number].CurrentMember *
null:[Time].[Year].CurrentMember *
null:[Time].[Month].CurrentMember)
,[Measures].[Movement]); FORMAT_STRING ( This ) = “#,#”;
End Scope;
this code showing result #VALUE!
Hi Abid
You need to remove the periodstodate() function, as you’re achieving this functionality yourself by using the {null:[Time].[xxx].CurrentMember} approach, which effectively does ‘All Members To Date’.
Thanks
Alex
thanks
****How to write MDX/DAX for Prior Year Comparison for 445 or custom calendar
Hi Alex,
How are you? I’m currently working on AAS Tabular model where I need to create some one the calculations with DAX. I’m struggling to create formula for custom/445 calendar which different than regular calendar where one week can have different number of days in current year vs prior year .
As an example lets say you have 10 days in week 52 of 2017 and 8 days in week 52 of 2016, which 8 days we will map those?
I am not sure since number of days in week vary year by year how will one be able to map less days in week of prior year to more days in week of current year, as an example lets say you have 8 days in week 52 of current year and 10 days in week 52 of prior year, There are two less rows in current year so we will have to add phantom rows.
Would you please help me on this or give me one example of DAX/MDX formula you have already been experienced such scenario. i’m fine with MDX query if you doesn’t have DAX.
Will appreciate your help.
Cheers,
Niel
Good morning guys
I need to make a calculation in SSAS (cube)
That the idea would be: “The idea is that you can build the balance based on the value of the title and the amount downloaded, can you do it?
then the position of the stock will be up to the present date
In the case of accounts receivable will be the balance on the selected date
This would allow, for example, to see the evolution of the debit balance of a customer every month. ”
I made that shawl, but it does not work.
Can anybody help me?
“CALCULATE;
CREATE MEMBER CURRENTCUBE [Measures] .Saldo_Calc
AS Aggregate (PeriodsToDate ([EMISSION DATE]. [Hierarchy]. [(ALL)],
[EMISSION DATE] [Hierarchy] .currentmember), SUM (([Measures]. [VALUE] – [Measures]. [WRITE OFF]))
VISIBLE = 1; “
Hi Leonardo
Try something like this
CREATE MEMBER CURRENTCUBE [Measures].Saldo_Calc
AS SUM({NULL:[EMISSION DATE].[Hierarchy].currentmember}, [Measures].[VALUE])
– SUM({NULL:[EMISSION DATE].[Hierarchy].currentmember}, [Measures].[WRITE OFF])
VISIBLE = 1;
Regards
Alex
Hi Alex
Thanks for your help, but this error has happened.
Would you know why?
Error 15 Analyzer: The following syntax error occurred during an analysis: Invalid Token, Line 19, Shift 1, -. 0 0
Error 16 One or more errors were encountered without MDX script.
Sorry, needs a comma before VISIBLE
It is giving the error in the second SUM, but the strange thing is that it is exactly the same as above by taking the comma before the visible, but it happens in the first line NULL, SUM and currentmember. They look different in color from the second line, it’s still black.
Maybe the error could be on the second line? I do not know.
Can you help me please?
It could be HTML character replacement, for example the “-” being changed to a different similar character. Try typing it manually rather than copy/paste.
I already tried to type manually, but still the error continues.
That sign of – before SUM maybe can not be causing the problem?
I got it that way.
CREATE MEMBER CURRENTCUBE.[Measures].Teste
AS SUM(({NULL:[EMISSION DATE].[Hierarchy].currentmember}, [Measures].[VALUE])-
SUM({NULL:[EMISSION DATE].[Hierarchy].currentmember}, [Measures].[WRITE OFF])),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘TLX FIN ACCOUNTS RECEIVABLE VIEW’;
I think I am going to go crazy, My code doesn’t seem to work. I am trying to calculate Previous Year Total across Transaction date or Accounting period depending on whichever is selected in the filters .
My MDX Code looks like below:
( [Inception Date].[Aggregation].&[Total Previous Year] )
= (
[Inception Date].[Aggregation].DefaultMember,
Ancestor( ParallelPeriod( [Transaction Date].[Calendar].[Calendar Year],
1,
[Transaction Date].[Calendar].CurrentMember ),
[Transaction Date].[Calendar].[Calendar Year] )
*
Ancestor( ParallelPeriod( [Transaction Date].[Calendar].[Calendar Year],
1,
[Transaction Date].[Calendar].CurrentMember ),
[Transaction Date].[Calendar].[Calendar Year] )
);
Could you please point out what I am doing wrong. Any help is appreciated.
Sorry, following is my code
( [Inception Date].[Aggregation].&[Total Previous Year] )
= (
[Inception Date].[Aggregation].DefaultMember,
Ancestor( ParallelPeriod( [Transaction Date].[Calendar].[Calendar Year],
1,
[Transaction Date].[Calendar].CurrentMember ),
[Transaction Date].[Calendar].[Calendar Year] )
*
Ancestor( ParallelPeriod( [Dim Accounting Period].[Accounting Period Calendar].[AP Calendar Year],
1,
[Dim Accounting Period].[Accounting Period Calendar].CurrentMember ),
[Dim Accounting Period].[Accounting Period Calendar] )
);
In your code you’ll get an error using ancestor if the ‘All’ member is selected, because ‘all’ doesn’t have an ancestor at the year level.
In this situation I’d say that you’re better off using scope to define two separate calculations depending on what is selected. You’ll certainly get better performance.
Regards
Alex
Ok I will create different measures for Current Year. There is one more measure I am struggling with. I used what you have suggested in your earlier comments for YTD. But this doesn’t seem to work for me. COuld you help me out?
Following is the code
([Inception Date].[Aggregation].&[Year to Date] )
= Aggregate(
{ YTD(TAIL(DESCENDANTS([Transaction Date].[Calendar].CurrentMember,,LEAVES),1).item(0))
}
*{ YTD(TAIL(DESCENDANTS([Dim Accounting Period].[Accounting Period Calendar].CurrentMember,,LEAVES),1).item(0))
}
* [Inception Date].[Aggregation].DefaultMember
);
Hi Alex,
I am writing this with great hopes and i’m sure that you will give me solution. Below is my schema for dim_calendar shared dimension. It has multiple hierarchies. I tried with all possible ways from blog and Mondrian documents. I tried to change Has ALL property as well as tried with different table (physical view in Mysql), but no luck. Please guide me.
MDX Query throw below error
Mondrian Error:Failed to parse query ‘SELECT NON EMPTY ([Measures].[TotalSale]) ON Columns,
NON EMPTY([Calendar].[week].members) ON Rows
FROM [sales_items]’
Mondrian Error:MDX object ‘[Calendar].[week]’ not found in cube ‘sales_items’
Thanks,
HK
Hi Harry
The error message points directly to a typo or name error in the Calendar.Week attribute. You called it dim_calendar in your explanation, check that it shouldn’t be [dim_calendar].[week].members to match your dimension design.
Regards
Alex
Thanks Alex for your reply.
In workbench schema, if we use multiple hierarchy within dimension then we must use hierarchy name with dimension name. i.e. NON EMPTY([Calendar.weekly].[week].members) ON Rows
Here “weekly” is hierarchy name within dimension. I could solve it.
I think i’m unable to upload XML tags for dim_calendar table.
Hi guys
Does anyone understand to slow change dimension?
I really need help about this..
(Sorry my bad english)
Hi Leonardo
I’m afraid that’s too big a question, SCDs are a detailed topic in their own right. For example the difference between type 0, 1, 2, 3 & 6, and then determining the best method of implementing them depends on the technology you’re using and the nature of the data.
I’d recommend either engaging a BI expert to go through it in detail, or there are plenty of blog posts which discuss this in detail.
Thanks
Alex
Hi Alex.
The big problem was that the dimensions work normal, but the “cube”, which is giving error
Can you be more specific? If you can post the error message then I may have a chance of helping.
This error: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘DGS_CUSTOMERS_SCD’, Column: ‘ROWID’, Value: ‘254301’. The attribute is ‘ROWID’. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute ROWID of Dimension: Invoices from Database: DIATEC_TELEMARKETING_CUBE, Cube: DIATEC_TMK_CUBE, Measure Group: DGS CUSTOMERS SCD, Partition: DGS CUSTOMERS SCD, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the ‘DGS CUSTOMERS SCD’ partition of the ‘DGS CUSTOMERS SCD’ measure group for the ‘DIATEC_TMK_CUBE’ cube from the DIATEC_TELEMARKETING_CUBE database.
Your fact table [DGS CUSTOMERS SCD] contains a reference to the [Invoice] dimension, for a record with ROWID=254301, which doesn’t exist in the dimension.
Perform a process update on the dimension first to load the new records, then reprocess your measure group.
Although I question whether your cube design is right; the SCD would normally be a dimension, and Invoice would normally be a measure group
Thanks 🙂
Hi Alex
Is there a way to use “OR” condition in MDX like
(Serialnumber = 111 OR user_Serialnumber= 11)
Thanks a lot for your help.
Regards,
Anub
Hello Alex,
I am trying to get a value for 12th month of all previous years using a date hierarchy and I have the following code:
CREATE MEMBER CURRENTCUBE.[Measures].[ITD x]
AS aggregate(({null:Ancestor( ParallelPeriod( [Evaluation Date].[Evaluation Date Calendar].[Year],1,
[Evaluation Date].[Evaluation Date Calendar].CurrentMember ),
[Evaluation Date].[Evaluation Date Calendar].[Year] )
}*[Evaluation Date].[Month Val].LASTSIBLING
),[Measures].[x]);
There is something wrong with my code as I am able to sum all previous years but not able to filter the 12th month. Could you help me with this? Thank you in advance
Try something like this:
CREATE MEMBER CURRENTCUBE.[Measures].[x Month12]
AS ([Measures].[x], [Evaluation Date].[Month Val].[12]);
CREATE MEMBER CURRENTCUBE.[Measures].[ITD x]
AS AGGREGATE({null:[Evaluation Date].[Evaluation Date Calendar].[Year].PrevMember}
, [Measures].[x Month12]);
Just sum all previous years, but separately filter to only month 12.
Thank you Alex. Let me try your suggestion.
Hi Alex, thanks for being a great resource for all of us.
I have an MDX issue where I have two fact tables: One for new business info and another for KPI info. They both use the same date dimension and both join on date keys. I’m trying to use your MDX code to allow for the created date on the first fact table to filter on the KPI target date on the second fact table. I’m doing this as work-around for Power BI so that I can have one filter work with these two cube date dimensions. The code below for a new calculation only works with the kpi target date and not the new business created date. Do you have any suggestions? I’ve spent several hours on this and am really stumped!
AGGREGATE(
{[Policy Created Date].[Date]}
* {[Target Date].[Date]}
, [Measures].[App Count Actual]
)
Hi Cole
To do this you need to use the LinkMember function, to link a date selected in one dimension across to also filter the same date in the 2nd dimension. There’s an example of how to use this at the following post:
https://purplefrogsystems.com/2013/04/mdx-between-start-date-and-end-date/
Regards
Alex
That works! Thanks for the help. I will keep this one in the toolbox for later use.
HI Alex,
We are facing some weird behavior in Mondrian, when we query the database for 1st jan to 31st jan, we get say like 20k records. And when we change the date range to 2nd jan to 31st jan, we get 800 records (which is the correct data). Why the Mondrian behaving wrong in case of 1st jan.
Thanks in advance
Hi Tarun
My guess is that you have either a scoped assignment overriding the calculation for the 1st, or maybe a problem in your partition source queries causing duplicate data.
Regards
Alex
Hi,
I’ve been struggle for 3 days trying to get a cumulative value accross multiple date hierarchies.
I tried your trick and works fine when i’m using attributes that are part of hierarchies:
CREATE MEMBER CURRENTCUBE.[Measures].[Closing Quantity]
AS AGGREGATE(
{null:[Date].[Calendar].CurrentMember}
* {null:[Date].[Calendar Year – Month].CurrentMember}
* {null:[Date].[Calendar Year – Week].CurrentMember}
* {null:[Date].[Date].CurrentMember}
, [Measures].[Unit])
The problem is I have 2 attributes: Year and Month to let users able to put years on rows and months on columns. But when using Year and Month , the closing quantity on months are resetting every year. I want the total from beginning of time. Also, if I use Month alone, I still have closing quantity per month but since I don’t have Year in my table I don’t want Closing Quantity to show values since like January will be all january together (so 10 years), etc
Hi Wilhuff,
Yes you have a challenging problem here – the challenge is that ‘Month’ on its own doesn’t have any concept of years; month 1 is month 1 regardless of what year the data is in.
So to get true cumulative Sum to Date you can’t use Month on its own, unless you set the Month column’s key to be Year-Month, but this changes the user experience.
You can however use some MDX trickery in a SCOPE, to map a user’s selection of Year and Month into a combined Year-Month, to get cumulative calculations working. Something like this:
//If they have selected the measure, and a year, and a month
SCOPE ([Measures].[Closing Quantity], [Date].[Year].[Year].MEMBERS, [Date].[Month].[Month].MEMBERS);
THIS = SUM({null:DESCENDANTS([Date].[YQMD].CURRENTMEMBER, [Date].[YQMD].[Month]).item([Date].[Month].CURRENTMEMBER.MEMBER_NAME-1)}, [Measures].[Quantity]);
END SCOPE;
Hi Alex –
I have two date dimensions (acting as Role Playing) – Order Date and Delivery Date, I have to create a calculated measure based on Sales Amount which should give me Rolling 12 Month Sales based on both the date dimensions.
Please advise how this can be created?
PS – I tried the below calculation, but it’s only on top of Sales Amount, how I can incorporate the logic of Rolling 12 Month Sales in this MDX? Also, upon browsing this particular measure against other dimensions (which are not mentioned here), it is showing the actual value of Sales Amount and I want it to show NULL or 0 as the value there.
AGGREGATE(
{NULL: [Order Date].[Order Date].CurrentMember}
* {NULL: [Delivery Date].[Delivery Calendar].currentmember}
, [Measures].[Internet Sales Amount])