Time-dimensioned Measures, Calculated Column, and Disconnected Slicer

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

 

Time-dimensioned Measures, Calculated Column, and Disconnected Slicer

Creating a time-dimensioned measure is simple with DAX and functions like DATEADD(). It works especially well with pivot tables and slicers. Suppose the requirement is a rolling 13-month line chart of revenue based upon an end-month selected on a slicer? Pretty simple, write 13 measures each incrementally offset from the base measure by -1 month.

Is there a better way to create relative time dimensions?

Here’s what I’ve tried and ran into a dead-end:

The idea was to create a disconnected date slicer the selection of which would affect a calculated column in the primary date table. Let’s call the primary date table, p_Date, and the disconnected date table d_Date.

d_Date has a single measure: MaxEOMDate:= EOMONTH(MAX(Date),0)

p_Date has a calculated column, RelativeMonth =
=SWITCH(TRUE(),
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],0),”MoCurrent”,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-1),”MoPrior01″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-2),”MoPrior02″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-3),”MoPrior03″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-4),”MoPrior04″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-5),”MoPrior05″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-6),”MoPrior06″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-7),”MoPrior07″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-8),”MoPrior08″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-9),”MoPrior09″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-10),”MoPrior10″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-11),”MoPrior11″,
EOMONTH(p_Date[Date],0)=EOMONTH(d_Date[MaxEOMDate],-12),”MoPrior12″
)

The column values are either a text of the month of the primary table’s row date value or null, if the date is outside the 13-month period.

The Transaction table’s transaction date has a relationship to p_Date[Date].

Here’s the intended use:
Create a pivot table. A slicer of d_Date’s month_year column provides the user with an end-month to select. The selection sets the MaxEOMDate value, which then identifies the RelativeMonth calculated column values. RelativeMonth provides values for the pivot table’s Column Labels. Whatever transaction measure would then be aggregated by RelativeMonth. Hola! Relative time-dimensioned measures using a disconnect slicer. Just one, small problem. It doesn’t work. :*(

My best guess of why it doesn’t work lies in the difference between calculated columns and calculated measures. Calculated columns are calculated upon refresh of the table, whereas measures are calculated when used. Given that d_Date is a disconnected table when p_Date is refreshed the RelativeMonth says, “Okay, what is the maximum date in the d_Date table?” The result is then used for the column. What the column’s calculation doesn’t see, because the tables are disconnected, is the change in the maximum date when the user selects a month_year from the slicer. Ooooo, so close!

So, that’s where the dead-end landed.

Is there a better way to create dynamic relative time-dimensioned measures for a rolling 13-month period other than writing 13 measures?

Rob and others, what do you think?

Cheers everyone and hope you have a memorable Memorial Day weekend!

Ron

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Ron,

I have created an Excel data model using your information and, you are right, it does not work :(.

Most of your thoughts on why it does not work, I agree with:

1) [the problem] lies in the difference between calculated columns and calculated measures (I agree)
2) Calculated columns are calculated upon refresh of the table, whereas measures are calculated when used (I agree)
3) Given that d_Date is a disconnected table when p_Date is refreshed, the p_Date[RelativeMonth] says, “Okay, what is the maximum date in the d_Date table?” (Indirectly, yes, but I believe what is really asked is, “What is returned from the measure [MaxEOMDate]” and then the answer to that question is evaluated based on the current row context of p_Date.

So the lesson that I learned in this example is that a slicer does not have any effect on how a calculated column is calculated.

But I also believe that your objective is possible with a disconnected table/slicer that would affect a measure like [MaxEOMDate] to influence other measures (not calculated columns).

Ron, let me know what you want to do next, I would like to follow along.

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

Hi Thomas,

Thank you (!) For your very thoughtful reply. 🙂

Another approach I took was to create 13 time-dimensioned measures in the d_Date table, which8ch were referenced in the RelativeMonth calculated column. Same results, or I should say, lack of results. Again, it appears the calculated column is unaware of the filter placed upon d_Date table by the slicer selection.

Unless someone more knowledgeable than I figures out a solution the only approach I see for a individual month results over a rolling 13-month period is the creation of 13 individual measures with incremental month offsets.

Of course, a different approach is to calculate the time dimensions within the Excel environment through CUBE functions.

My goal is to have time-dimensioned measures usable in a pivot table and easily called by a CUBE function or within a SSAS tabular model. Define once, reference many.

Oh well, guess it’s back to creating lots of measures.

Cheers,

Ron

[Author]

Posts