Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pmdci
Advocate V
Advocate V

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

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.

 

 

1 ACCEPTED SOLUTION
pmdci
Advocate V
Advocate V

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 🙂

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Advocate V
Advocate V

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 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.