cancel
Showing results 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

Helper III

## Calculated Column "Months Ago"

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"

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver II

Hi there,

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

Thanks,

J