Data Issue for DateDiff or Daysbetween

DAX DAX Calculations
1

I’m trying to calculate the days between Date of Service and today.
Which on the surface should be super easy BUT clearly there’s something within my Date of Service column data/structure that isn’t allowing BI to recognize the date or fields.

I’ve narrowed my full data set to 2 tables - Date & the AR information (identifying info removed) and I still can’t get the InsData[Date of Service] field to be recognized for any date calculation.

I’m getting an aggregation error as shown in this snip which adds to my confusion as the column does show as a date format within the data set structure.

Aggregation Error
Aggregation Error3000×1904 381 KB

DaysBetween.pbix (167.7 KB)
PBIX is also attached.

I’m sure there’s a simple data structure detail I’m missing but for the life of me I’m not seeing it.

Thanks in advance.

gina

2

Hello @gkavesh,

Thank You for posting your query onto the Forum.

The reason why you’re getting this kind of an error is because you’re simply referencing the naked column/field from the table while creating a measure. Had you been creating a calculated column it would’ve worked but in the case of measure, it needs to be wrapped with the function and in this case it’ll “SELECTEDVALUE()” function.

So based on the condition that you’ve specified below is the measure alongwith the screenshot of the final results provided for the reference -

Days Difference = 
DATEDIFF(
    SELECTEDVALUE( InsData[Date of Service] ) , 
    TODAY() , 
    DAY )

Final Results
Final Results568×767 9.67 KB

I’m also attaching the working of the PBIX file for the reference purposes alongwith the links of the thread which covers similar types of solutions.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

DaysBetween - Harsh.pbix (167.7 KB)

3

Thank you - that’s an nuance of DAX I did not know…Appreciate the explaination and solution.