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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

Thanks,

J

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors