Forum Discussion

RWWRRW's avatar
RWWRRW
Regular Visitor
3 years ago

Days Beetween deliverydates

I have a table with different order numbers, different materials and different delivery dates. I would now like to have the number of days of the delivery date per material evaluated. In Excel I had...
Need Help
Show and Tell
RWWRRW's avatar
RWWRRW
Regular Visitor
3 years ago

sorry i hope this table shows it. I want in Power Bi via Dax calculate the days beetween the deliverdates from each part

POPartDate LfDdays
551196471107.06.2023 
551197471119.06.202312
82706078471223.06.2023 
82693981471230.06.20237
5555125471307.06.2023 
5555126471322.06.202315
6238279471529.06.2023 
6238447471529.06.20230
6240493505016.06.2023 
6238006505024.06.20238
552230505301.06.2023 
552230505315.06.202314
552230505329.06.202314
62404977890220.06.2023 
62405767890220.06.20230
62409157890227.06.20237
danextian's avatar
danextian
Super User
3 years ago

Hi RWWRRW

 

You can use RANKX to rank the dates by part and another calc column to get the previous date

DateRank = 
RANKX (
    FILTER ( 'Table', 'Table'[Part] = EARLIER ( 'Table'[Part] ) ),
    'Table'[Date LfD],
    ,
    ASC,
    DENSE
)

PrevDate (RANKX) = 
CALCULATE (
    MAX ( 'Table'[Date LfD] ),
    FILTER (
        FILTER ( 'Table', 'Table'[Part] = EARLIER ( 'Table'[Part] ) ),
        'Table'[DateRank]
            = EARLIER ( 'Table'[DateRank] ) - 1
    )
)

Or a single calc column to for the previous date

PrevDate (not RANKX) = 
CALCULATE (
    MAX ( 'Table'[Date LfD] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[PrevDate (RANKX)] < EARLIER ( 'Table'[PrevDate (RANKX)] )
    )
)

For larger tables, I prefer the RANKX version. Performance shouldn't be an issue with small tables.

 

Now to get the difference, you can use either of the two previous date column

Difference = 
IF ( 'Table'[PrevDate (RANKX)] = BLANK (), 0, 'Table'[Date LfD] - 'Table'[PrevDate (RANKX)] )