cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## 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 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?

8 REPLIES 8
Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
Learn with me on YouTube @PowerBITambayan.
Regular Visitor

Hi

i have a table with PO/Parts/deliverydate

 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
Regular Visitor

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

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

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)] )
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
Learn with me on YouTube @PowerBITambayan.
Super User

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
Result``````

Output looks like this:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Regular Visitor

Not quite yet. It now correctly displays the daydiffernz from the first to the last delivery date. But the days between several delivery dates unfortunately not.
See marked in green.
For Part 5053 I expected from June 1 to June 15 (first delivery date to next delivery date)14 days difference from June 14 to June 29 (next delivery date to next) the difference of 14 days.
In the next part I have three delivery dates, here I would have expected as result 0 and in the second the 7 days day difference.
There can also be several delivery dates per part

Super User

Hello @RWWRRW 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.

Your measure should look like this:

``Duration = Calculate(Networkdays(Start_Date,End_Date),ALLEXCEPT(Table_Name, 'Table_Name'[Material_Column]))``

This will give you the duration for each material individually.

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Solution Supplier

Can you please share your sample data?

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors