Calculate Difference Between Dates in Same Column

[Anonymous]

Hi - 

I am new to PowerBI and hoping for some help with finding the number of days between dates in the same column,.  I need to find the number of days between orders by the same customer with a different invoice number.  A sample of how the data is laid out is below, along with the desired result calcuated manually in excel.

I have tried combinations of DATEDIFF, EARLIER, TOPN and other functions but have not been able to find a solution.

Thanks for your help!

InvoiceDate	InvoiceNo	Customer	Days Between Previous Order
4/4/2019	34567	Henry	0	<---need to make sure there is no error since this is the first order
4/4/2019	34567	Henry	0
4/4/2019	34567	Henry	0
4/21/2019	87654	Sarah	0
5/2/2019	50000	Jill	0
5/2/2019	50000	Jill	0
5/7/2019	78906	Henry	33
5/7/2019	78906	Henry	33	<--- need to find the #days since Henry ordered a different invoice #
5/7/2019	78906	Henry	33
5/20/2019	56789	Sarah	29
5/20/2019	56789	Sarah	29
5/20/2019	56789	Sarah	29
6/1/2019	90000	Henry	25
6/1/2019	90000	Henry	12
6/7/2019	32892	Jim	0
6/7/2019	32892	Jim	0
6/7/2019	32892	Jim	0

[Stachu · Accepted solution]

code for a calculated column, it's all based on assumption that an invoice only has a single date (no collective invoicing etc.)

Column =
VAR __CurrentCustomer = 'Table'[Customer]
VAR __CurrentInvoiceDate = 'Table'[InvoiceDate]
VAR __CurrentInvoiceNo = 'Table'[InvoiceNo]
VAR __PreviousInvoiceDate =
    CALCULATE (
        MAX ( 'Table'[InvoiceDate] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = __CurrentCustomer
                && 'Table'[InvoiceNo] <> __CurrentInvoiceNo
                && 'Table'[InvoiceDate] < __CurrentInvoiceDate
        )
    )
RETURN
    IF (
        ISBLANK ( __PreviousInvoiceDate ),
        BLANK (),
        'Table'[InvoiceDate] - __PreviousInvoiceDate
    )

it may also not give correct results if customer has multiple invoices on the same day (difference will be 0). This can be avoided if the invoice numbers are always increasing and numerical (assumption that previous invoice always has a lower number, but the matter of cancellations comes to mind then)

[Anonymous]

Thanks! I tried this but all the rows in the column came back blank. I also tried removing the If blank language (see below), but it still came up blank.

Any ideas on what this might be?

[Stachu]

try removing the ".[Day]" after the invoice date

[Anonymous]

thanks @Stachu !  this worked! my one addition was to add +1 to the return (SalesExport[InvoiceDate] +1- _PreviousInvoiceDate), otherwise it would give the number of days one day off, and as 31 for some reason if there was an invoice the day before.

my only problem now is this only seems to work if stored as a date value and shown visually as a Day within the date hierarchy, which means I can't sum the column or perform other operations like average etc.

[Stachu]

you need to change the data type of this column to Whole Number in the Modeling ribbon (possibly also format) - that should do the trick, PowerBI sets it up as a date by default

[Anonymous]

thanks so much! for some reason didn't work the first go, but tried again and its working!

thanks again!!

[Anonymous]

thanks so much! for some reason didn't work the first go, but tried again and its working!

thanks again!!

[Anonymous]

thanks @Stachu ! this worked now!

the only thing I had to change was adding a +1 in the RETURN section - otherwise for dates that had the prevoius date the day before, it was returning the day difference as 31

RETURN

IF(

ISBLANK(_PreviousInvoiceDate),

BLANK(),

SalesExport[InvoiceDate] +1- _PreviousInvoiceDate

)

the only problem now is that this only seems to work if stored as a date and show visibly as DAY within the Date hierarch - which means I can't sum the column or use for other calculations such as average etc.