cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

Thanks,

J

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors