Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Please provide more information/sample data. It will matter the days to compare are in separate tables, different rows, etc.
Proud to be a Super User!
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 |
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 |
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)] )
Proud to be a 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!!
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
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!!
Can you please share your sample data?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |