0
votes

I'm new of MDX and I'm trying to calculate a new measure based on two different date dimensions. I have the Creation Date Dimension (with Year, Trimester, Month, Day) and Resolution Date (with Year, Trimester, Month, Day).As measure I have the number of tickets and I want to calculate two new measures in order to know how many tickets that were resolved this month were actually created last month and how many tickets were resolved in the same month as they were created.

I found this interesting post, but I cannot understand how to use properties.. https://bennyaustin.com/2012/06/05/ssas-mdx-calculated-measures-that-require-date-comparison/

Any ideas or suggests? Thanks for your help.

1

1 Answers

0
votes

This question cannot be answered as asked without knowing the exact definition of your time dimensions.

An approach which might be worth considering if your MDX knowledge is little, but you have some SQL knowledge and if your requirements can be fixed to just the month level as you described, could be the following, which does the main calculations already when loading the cube, and not at query time in MDX:

Add a column 'months_from_creation_to_resolution' to your fact table, possibly just add this as a column in the view you may already use on the fact table. This column would be 0 if the ticket was resolved in the same month where it was created, 1 if it was resolved in the month after creation, 2 if it was resolved e. g. in May and created in March, etc. You do this calculation using SQL date functions. You would then create a new dimension in your cube from this table, which would have the new column as the only attribute. SSAS has no problem using a table as base for both a measure group and a dimension.

Then, in MDX, the number of tickets resolved in the month they were created would just be

([Measures].[TicketCount], [Fact].[months from creation to resolution].[0])

and those resolved in the month after creation would be

([Measures].[TicketCount], [Fact].[months from creation to resolution].[1])

As a side effect, the query run time would be faster, as the main logic is pre-calculated when loading the cube.