Days Beetween deliverydates

Question

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 the function Days.
In Power Bi this does not work with Datediff.

How i can solve it via a DAX Formula? A Measure oer a calculated Column?

kishore_kvn · Answer

You should have explained it better. Any ways here is the solution.Use this measure to get days.Duration = 
Var Start_Date = CALCULATE(MIN('Table'[Date LfD]),ALLEXCEPT('Table','Table'[Part]))
Var End_Date = CALCULATE(MAX('Table'[Date LfD]),ALLEXCEPT('Table','Table'[Part]))
Var Result = DATEDIFF(Start_Date,End_Date,DAY)
Return
ResultOutput looks like this:&nbsp;If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!&nbsp;

danextian · Answer

Hi&nbsp;RWWRRW
&nbsp;
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)] &lt; EARLIER ( 'Table'[PrevDate (RANKX)] )
    )
)

For larger tables, I prefer the RANKX version. Performance shouldn't be an issue with small tables.
&nbsp;
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)] )

&nbsp;
&nbsp;

dhairya · Answer

Can you please share your sample data?

kishore_kvn · Answer

Hello&nbsp;RWWRRW&nbsp;you can use NETWORKDAYS DAX and create a measure and dont forget to mention ALLEXCEPT for the material column so that it will pick days for each material individually.&nbsp;Your measure should look like this:Duration = Calculate(Networkdays(Start_Date,End_Date),ALLEXCEPT(Table_Name, 'Table_Name'[Material_Column]))&nbsp;This will give you the duration for each material individually.&nbsp;&nbsp;If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

danextian · Answer

Please provide more information/sample data. It will matter the days&nbsp; to compare are in separate tables, different rows, etc.

29.06.2023
6238447	4715	29.06.2023	0
6240493	5050	16.06.2023
6238006	5050	24.06.2023	8
552230	5053	01.06.2023
552230	5053	15.06.2023	14
552230	5053	29.06.2023	14
6240497	78902	20.06.2023
6240576	78902	20.06.2023	0
6240915	78902	27.06.2023	7

PO	Part	Date LfD	days
551196	4711	07.06.2023
551197	4711	19.06.2023	12
82706078	4712	23.06.2023
82693981	4712	30.06.2023	7
5555125	4713	07.06.2023
5555126	4713	22.06.2023	15
6238279	4715	29.06.2023
6238447	4715	29.06.2023	0
6240493	5050	16.06.2023
6238006	5050	24.06.2023	8
552230	5053	01.06.2023
552230	5053	15.06.2023	14
552230	5053	29.06.2023	14
6240497	78902	20.06.2023
6240576	78902	20.06.2023	0
6240915	78902	27.06.2023	7

Forum Discussion

Days Beetween deliverydates

8 Replies

Related content

Data Days | Data Days Your Way

Day to Day average

How many days beetween 2 dates?

Data Days | Create

Cumulative Sales amount beetween 2 dates