Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Finding the last month with data

Posted by thomasivarssonmalmo on May 24, 2007

In sales cubes you normally have a time dimension that is extended into the future because of budget records or forecast records in the fact table. A budget cover time members for all months in the current year but actual sales follows the current date with a lag with one day.
 
If you have charts in a dashboard/portal you would like that the time axis, like month, should adjust dynamically when a new date of actual facts have been loaded into the fact table. To open each chart and change a hard coded time member to the last with data can be avoided with MDX- named sets.
 
The challange is to find a measure that tells what is the last month with data. 
 
This named set([RecentMonth] will find the last month with data depending on that [Sales USD] will indicate the last month with data.
 
Tail(Filter([Time].[Time_Calendar].[Month].Members,
(Time.[Time_Calendar].Currentmember,[Measures].[Sales USD])>0))
 
With that set([RecentMonth]) we can construct other named sets like this([PreviousMonthsThisYear]):
 
{YTD(RecentMonth.Item(0).Item(0).Lag(1))}
 
And finally you can construct a rolling 12 months dynamic time axis with this MDX:
RecentMonth.Item(0).Item(0).Lag(13):RecentMonth.Item(0).Item(0).Lag(1)
 
If this is a working solution for you or not depends on if the time dimension have members that goes further than the current month.
 
Without budget or future records in the fact table the solution is much more simple than I have suggested.
Advertisements

2 Responses to “Finding the last month with data”

  1. Stefan said

    Just one small pitfall warning to add, one that i have fallen into myself. That is when you have "small" amount of activity in the fact table(for instance sales does not happen every day in a some companies) and you try to use this approach when what you are querying for is actually current month/week/date, time wise(regardless if there are transactions or not). Then there could be a situation where you want it to move forward but does not, because there are no transactions on the later dates. Then you actually have to use a function that returns the actual date(like vba and/or excel functions, or your own stored procedure).  

  2. Thomas said

    Good point Stefan I have used these sets in a manufacturing company where all fact records arrived on time but I will remember you comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: