Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DSwezey
Helper III
Helper III

Calculate number of days between Selected Date and another Table

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. 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @DSwezey ,

 

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

Eyelyn9_0-1657520706819.png

Output:

Eyelyn9_1-1657520725074.png

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.

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @DSwezey ,

 

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

Eyelyn9_0-1657520706819.png

Output:

Eyelyn9_1-1657520725074.png

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
Super User
Super User

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')
)

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.