Difference between due date and selected calendar date

[ganchevd]

Hello,

I have an issue by making the calendar slicer affect the days overdue. From the table bellow:

Customer name	Cust No.	Document date	Document No.:	Due date	Invoiced	Ending balance
BATHBASH	204398678	04.12.2017	100012830	14.12.2017	341.78	341.78
MENIN	25014	01.03.2018	100013308	31.03.2018	600.00	600.00
ASYAS	202510210	11.04.2018	100013525	14.04.2018	126.00	126.00
LOANGROUP	205088248	03.05.2018	100013598	06.05.2018	84.00	84.00
CENTURY	204591766	02.05.2018	100013589	12.05.2018	1 144.92	1 144.92
OK CORP	101767165	02.05.2018	100013584	12.05.2018	120.00	120.00

I'd like to add another column called "Days Overdue", which to return the number of overdue days, based on the selected date from my calendar table. I've tried som variations of DATEDIFF but neither worked. I have a separate calendar table, linked with the DataTable, One to Many.

I would appreciate it very much if you help me with this issue. I think I am very close but not exactly.

[Greg_Deckler · Accepted solution]

You would need to create a measure, which should be something like:

Measure = 
VAR MaxDate = MAX('Calendar'[Date]) // Or MAXX(ALLSELECTED('Calendar'),[Date])
VAR DueDate = MAX('Table'[Date])
RETURN DATEDIFF(MaxDate,DueDate,DAY)

[ganchevd]

Thank you. It is working. It changes dates but when I try to add the column into the table above it breaks. Probably something with the link between calendar table and the data table. Is there any difference if the Calendar Table is prepared in Power Query Mode? Is there a way to do it as Calculated column. Since the data is huge and it is refreshing very slowly. I tried to put the same formula in Column, but it did not work properly. I received one and the same value for each of the rows.

[v-chuncz-msft]

@ganchevd,

Values in a calculated column are fixed. They are an immutable result for each row in the table.