Solved: Re: DATEDIFF

erezbenmoshe · ‎10-30-2019

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

jdbuchanan71 · ‎10-30-2019

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.

View solution in original post

nilimarodrigues · ‎10-30-2019

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 · ‎10-30-2019

Hi @erezbenmoshe ,

Works here.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

jdbuchanan71 · ‎10-30-2019

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.