can't calculate DATEDIFF using Max function

[darlingling520]

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?

thanks in advanced! 

[Samarth_18 · Accepted solution]

Hi @darlingling520 ,

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

[darlingling520 · Accepted solution]

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 

[Samarth_18 · Accepted solution]

Hi @darlingling520 ,

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

[darlingling520]

thanks so much!!