Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RWWRRW
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
danextian
Super User
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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi

i have a table with PO/Parts/deliverydate

29.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

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

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

danextian_3-1686613014995.png

 

 










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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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:

Kishore_KVN_0-1686206144165.png

 

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

 

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

 

expected result 2.png

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

Dhairya
Super User
Super User

Can you please share your sample data?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.