Calculate number of days between Selected Date and another Table

[DSwezey]

I have an Invoice table that has "InvoicedDate".

I have a date table as well.

The date table and the Invoice table are related on 'Date'[Date] ----> 'Invoice'[InvoicedDate]

On my dashboad I have a date slicer via 'Date'[Date] that when I change I want to count the number of days between the Max('Date'[Date]) and the 'InvoicedDate' per invoice.

I tried using Datediff, but was unsuccessful. 

I also need this to be a measure rather than a calculated column. 

Any help would be appreciated. 

[v-eqin-msft · Accepted solution]

Hi @DSwezey ,

Please delete the relationship or make the relationship inactive between 'Date'[Date] and 'Invoice'[InvoicedDate]

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Your date slicer would filter a table visual to only show those invoices which have an invoice date matching the slicer selection. If that is not the behviour you want you can either use Edit Interactions to disable the date slicer from filtering the table visual, or you can create a new date table just for the slicer and have no relationship from that to the invoice table.

To get the number of days between the invoice date and the date in your slicer you can create a measure like

Days diff = CALCULATE(
DATEDIFF( MAX('Date'[Date]), SELECTEDVALUE('Invoice'[Invoice Date]),
REMOVEFILTERS('Date')
)

[DSwezey]

So I was able to get a measure working that calculates the amount of days between the MAX('date') and min('invoice'[date]) but this only gives me the difference between the "First" invoice date. It does not calculate it for each invoice date for each invoice number.

Days Diff = 
var firstday = MIN('INVOICE'[InvoiceDateTime])
var LastDay = MAX('DateTable'[Date])
return
datediff(firstday,LastDay,DAY

[DSwezey]

I've tried something similar to this but I still get (Blank) returned. Still not sure why this is happening