DATEDIFF

[erezbenmoshe]

DATEDIFF doesn't show the expected difference.  I am trying to calculate "Months from first purchase".  

Both columns are the same date format.  The difference I get is not correct.

This is a calcualted column:

Months from first purchase = DATEDIFF(FactSalesCache[FirstDate], FactSalesCache[Date], MONTH)

Thanks,

Erez

[nilimarodrigues]

Could it be your date format? I tried with MM/DD/YYYY format and DATEDIFF works perfectly fine.

Another alternate formula could be:

DIFFERENCE = month('SAMPLE'[FIRSTSALE])-month('SAMPLE'[FIRSTPURCHASE])

[Nathaniel_C]

Hi @erezbenmoshe ,

Works here.

[jdbuchanan71 · Accepted solution]

It looks like you pulled that column into a visual and the field is set to SUM.  My guess is the second line represents 3 records in your data set (4 months diff * 3 = 12) and the third line is covering 2 records.  Try changing the field to show the AVERAGE.

[erezbenmoshe]

You are right!! I am embarrassed that I did not figure it out myself 🙂