Calculated column in SSAS tabular: integer showing months passed based on date field

[pmdci]

Hello,

Consider a simple fact table in which I have:

• CUSTOMER NAME
• DATE
• VALUE

(this is a overly-simplified example)

What I would like to do is add a calculated column called MONTHS ELAPSED, which counts the number of months passed since the DATE column. So considering that by the time I wrote this post we are in May 2016, it would look something like this:

CUSTOMER NAME,   DATE,          MONTHS ELAPSED,

Contoso,         20/May/2016,   0,

Contoso,         01/Apr/2016    1,

Contoso,         23/Apr/2016    1,

Contoso,         30/Mar/2016    2,

Contoso,         01/Mar/2016,   2,

Contoso,         10/Feb/2016,   3,

Contoso,         17/Jan/2016,   4,

Contoso,         05/Dec/2015,   5,

Now here is the catch: This model is in SSAS server based on SQL 2012 SP3.

I am hoping that this could be done with a calculated column. Note that the column looks strickly for the months, regardless of the day in the month. So if we are in 01/May/2016 and we look for rows in April/2016, the calculated column should show 1 regardless if it was on 01/Apr/2016 or 30/Apr/2016. With that in mind, the following formula WON'T WORK:

=IF(DAY(Today())>=DAY([DATE]),0,-1)+(YEAR(Today())-YEAR([DATE]) ) * 12 + MONTH(Today()) - MONTH([DATE])

The above formula would not work because it would consider something that happened late last month to be within the month. For example, if we are now in May 2016 it would count the entry with a date of 23/Apr/2016 to be 0 (incorrect), not 1 (which would be the correct one).

Since the model will be reprocessed daily at 01:00 and there is no use outside business hours, I do not see an issue with this column being invalidaded. 

Any ideas on how the formula would look like in this case? I am hoping that when we upgrade do SQL 2016 I will simply do:

=DATEDIFF([DATE], Today(), MONTH)

But until that happens, I am stuck with SSAS 2012 and I really need some help here 🙂

Thanks in advance for your help and support.

Regards,

P.

[Greg_Deckler]

How about this:

Calculated columns:

Today = TODAY()

MonthEvent = FORMAT([DATE],"yyyyMM")

MonthToday = FORMAT([Today],"yyyyMM")

MonthsElapsed = IF(YEAR([DATE])<YEAR([Today]),((YEAR([Today])-1-YEAR([DATE]))*12)+12-(MONTH([DATE])-MONTH([Today])),[MonthToday] - [MonthEvent])

[pmdci]

Humm... A lot of fields there. What about the previous solution that I found? That seems to work unless you believe there is something fundamentally flawed with it?

On another note, what would you do if the DATE field (not the TODAY() field) was on another table (say, a date dimension)? How would you go about it? I tried using RELATED() but it gave me an error. Any thoughts?

Regards,

P.

[pmdci · Accepted solution]

Found the solution, actually:

=(YEAR(Today())-YEAR(DATE]))*12+MONTH(Today())-MONTH([DATE])

Quite lame of me as it was in the same page as the original formula I found 🙂