Change in Number of Days

[Anonymous]

Hi

I'm trying to wrap my head around how to perform the following. All date formats are YYYY-MM-DD

I've the table below which consists of the following columns:

EXISTING COLUMNS

Serial: Serial Number of an item

Arrival to WH: The date where the material is expected to arrive to the warehouse.

FileDate: I get updates in XLSX file. So I extract the file date to record when the update arrived. The updates does not arrive every day and hence you can see gap in dates.

CALCULATED COLUMNS TO BE CREATED

PreviousFileDate: This is date of the file that is prior to the file in each row. e.g. row # 4, the file date in this row is 2022-09-02, the PreviousFileDate is 2022-08-31 and NOT 2022-08-01.

DaysDifferenceFromPreviousDate: This is difference in days between two consequitive ArrivalToWH.

ArrivalToWH in Row 2 - ArrivalToWH in Row 1

ArrivalToWH in Row 3 - ArrivalToWH in Row 2

....etc

ChangeFromLastFileDate: This is difference in days between the ArrivalToWH in the latest file/update and the ArrivalToWH in each row.

ArrivalToWH in Row 7 - ArrivalToWH in Row 6 ==> DateDiff (ArrivalToWH in Row 6, ArrivalToWH in Row 7)

ArrivalToWH in Row 7 - ArrivalToWH in Row 5 ==> DateDiff (ArrivalToWH in Row 5, ArrivalToWH in Row 7)

ArrivalToWH in Row 7 - ArrivalToWH in Row 4 ==> DateDiff (ArrivalToWH in Row 4, ArrivalToWH in Row 7)

ArrivalToWH in Row 7 - ArrivalToWH in Row 3

....etc

Serial	Arrival ToWH	FileDate	PreviousFileDate	ChangeFromPreviousDate	ChangeFromLastFileDate
10285193	2022-09-09	2022-08-29			0
10285193	2022-09-09	2022-08-30	2022-08-29	0	0
10285193	2022-09-09	2022-08-31	2022-08-30	0	0
10285193	2022-09-09	2022-09-02	2022-08-31	0	0
10285193	2022-11-02	2022-09-04	2022-09-02	-7	7
10285193	2022-11-10	2022-09-06	2022-09-04	8	-1
10285193	2022-11-09	2022-09-11	2022-09-06	-1

Thanks in advance

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a column by dax.

Column =
CALCULATE (
    MAX ( 'Table'[FileDate] ),
    FILTER (
        ALL ( 'Table' ),
        [FileDate] < EARLIER ( 'Table'[FileDate] )
            && [Serial Number] = MAX ( 'Table'[Serial Number] )
    )
)

Or create a measure by dax

Measure = CALCULATE(MAX('Table'[FileDate]),FILTER(ALL('Table'),[Serial Number]=MAX('Table'[Serial Number])&&[FileDate]<MAX('Table'[FileDate])))

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, but I'm not sure what are you returning with the new column?

[v-yalanwu-msft]

Hi, 

@Anonymous ;

Maybe I understand the error, can you tell me with an example the result and logic you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Power BI has no concept of row numbers. You need to indicate the order that your data should be processed in. Is FileDate guaranteed to be unique?

[Anonymous]

Hi @lbendlin  for each Serial Number, there is one update on each day. You can not find two updates for the same serial number in each filedate.

Below is example:

Serial Number	FileDate
10285193	2022-08-29
10285981	2022-08-29
10285776	2022-08-29
10285193	2022-08-30
10285981	2022-08-30
10285776	2022-08-30
10285193	2022-09-01
10285981	2022-09-01