Forum Discussion

pmdci's avatar
pmdci
Advocate V
10 years ago
Solved

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 E...
Need Help
  • pmdci's avatar
    pmdci
    10 years ago

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
pmdci
Advocate V
10 years ago

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.