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

New Member

## can't calculate DATEDIFF using Max function

Hi all,

I'm pretty new to power bi so I would like some help on this

I am trying to calculate the month difference between the max date and the selected date

I tried 2 different methods:

Method 1 using today() and it worked well

datediff = DATEDIFF(SELECTEDVALUE(DATA[date].[date]),today(),MONTH)

however when I tried to use the max function replacing the today() function- my result returned to 0
datediff2 = DATEDIFF(SELECTEDVALUE(DATA[date].[date]),max(DATA[date].[Date]),MONTH)

does anyone know why is my max() not returning the max date of the table?

2 ACCEPTED SOLUTIONS
Community Champion

You could try this:-

``````datediff2 =
var max_date = calculate(max(DATA[date]),ALL(DATA[date]))
return DATEDIFF(SELECTEDVALUE(DATA[date]),max_date,MONTH)``````

BR,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

New Member

okay I already figured out myself - in order to use the max function

i need to use maxdate= calculate(max(data[date]),all(data)); so the max date will be fixed and therefore i can use it to calculate the datediff

3 REPLIES 3
New Member

okay I already figured out myself - in order to use the max function

i need to use maxdate= calculate(max(data[date]),all(data)); so the max date will be fixed and therefore i can use it to calculate the datediff

Community Champion

You could try this:-

``````datediff2 =
var max_date = calculate(max(DATA[date]),ALL(DATA[date]))
return DATEDIFF(SELECTEDVALUE(DATA[date]),max_date,MONTH)``````

BR,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

New Member

thanks so much!!

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