Calculated Column "Months Ago"

[cplesner]

Hi

Can someone help me with the syntax for creating a calculated column that shows the number om months ago from today 12 months in both "directions". So current month shows 0, Last month = 1 and Next month = -1.

Eventually i would like to end up with an attribute in the time dimension where i can select

"more than 12 months into the future"

-12

-11

-10

...

...

10

11

12

"more then 12 months ago"

[v-yuezhe-msft · Accepted solution]

Hi @cplesner,

You can create the calculated column using the following DAX .

Column = IF(MONTH(Table[Date])=MONTH(TODAY()) && YEAR(Table[Date])=YEAR(TODAY()),0,IF(Table[Date]<TODAY(),DATEDIFF(Table[Date],TODAY(),MONTH),(-1)*DATEDIFF(TODAY(),Table[Date],MONTH)))

Thanks,
Lydia Zhang

[opticshrew]

Hi there,

I think what you are looking for is this function: Datediff()

You can find more information about it here: https://msdn.microsoft.com/en-gb/library/dn802538.aspx

Thanks,

J