Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys
I need your help.
I have data table with order nr and then 4 dates.
- Creation date (this is in date format, but also week and year),
- Req. week (only in week and year format)
- Last Conf. week (only in week and year format)
- First Conf. week (only in week and year format)
I need to calculate the difference between lets say creation week and req. week
* for example creation week is 25 and req. week is 48 the difference will be 48-25 =23
This works easy because the difference is a positive but if
creation week is 45 and the req. week is 5 then 5-45 = -40 which is not true because if year has 52 weeks then the difference should be 52-40 = 12 + 5 = 17
Can someone help me with this calculation.
I would prefer to have it as a column created in dax but M or Measure is also fine.
Thank you
Order # | Creation Day | Creation Week | Creation Year | First Conf. Week | First Conf. Year | Last Conf. Week | Last Conf. Year | Req. Del. Week | Req. Del. Year |
7640274 | 13.02.2018 | 7 | 2018 | 31 | 2018 | 31 | 2018 | 31 | 2018 |
8001406 | 15.07.2018 | 28 | 2018 | 6 | 2019 | 6 | 2019 | 6 | 2019 |
7591516 | 27.01.2018 | 4 | 2018 | 32 | 2018 | 32 | 2018 | 32 | 2018 |
8016360 | 20.07.2018 | 29 | 2018 | 6 | 2019 | 6 | 2019 | 6 | 2019 |
7514508 | 23.12.2017 | 51 | 2017 | 23 | 2018 | 23 | 2018 | 23 | 2018 |
7549968 | 10.01.2018 | 2 | 2018 | 22 | 2018 | 22 | 2018 | 22 | 2018 |
7704635 | 07.03.2018 | 10 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7758554 | 29.03.2018 | 13 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7742787 | 22.03.2018 | 12 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7730467 | 17.03.2018 | 11 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7692800 | 02.03.2018 | 9 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7666685 | 22.02.2018 | 8 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7597433 | 30.01.2018 | 5 | 2018 | 36 | 2018 | 36 | 2018 | 36 | 2018 |
7626236 | 09.02.2018 | 6 | 2018 | 32 | 2018 | 32 | 2018 | 32 | 2018 |
7764256 | 02.04.2018 | 14 | 2018 | 38 | 2018 | 37 | 2018 | 37 | 2018 |
7635722 | 16.05.2018 | 20 | 2018 | 38 | 2018 | 36 | 2018 | 31 | 2018 |
7523304 | 27.12.2017 | 52 | 2017 | 28 | 2018 | 36 | 2018 | 28 | 2018 |
7912200 | 05.06.2018 | 23 | 2018 | 50 | 2018 | 45 | 2018 | 45 | 2018 |
7299893 | 19.09.2017 | 38 | 2017 | 15 | 2018 | 15 | 2018 | 15 | 2018 |
7229250 | 28.08.2017 | 35 | 2017 | 22 | 2018 | 19 | 2018 | 10 | 2018 |
7151335 | 03.08.2017 | 31 | 2017 | 11 | 2018 | 8 | 2018 | 5 | 2018 |
7138822 | 28.07.2017 | 30 | 2017 | 9 | 2018 | 9 | 2018 | 9 | 2018 |
7552129 | 19.01.2018 | 3 | 2018 | 18 | 2018 | 18 | 2018 | 18 | 2018 |
6633454 | 17.12.2016 | 50 | 2016 | 27 | 2017 | 26 | 2017 | 26 | 2017 |
6469277 | 07.10.2016 | 40 | 2016 | 12 | 2017 | 18 | 2017 | 7 | 2017 |
6347328 | 17.08.2016 | 33 | 2016 | 12 | 2017 | 12 | 2017 | 12 | 2017 |
6948076 | 10.05.2017 | 19 | 2017 | 44 | 2017 | 44 | 2017 | 44 | 2017 |
6413606 | 09.09.2016 | 36 | 2016 | 13 | 2017 | 14 | 2017 | 8 | 2017 |
6429257 | 16.09.2016 | 37 | 2016 | 20 | 2017 | 20 | 2017 | 20 | 2017 |
6335219 | 12.08.2016 | 32 | 2016 | 11 | 2017 | 11 | 2017 | 11 | 2017 |
5418825 | 21.08.2015 | 34 | 2015 | 6 | 2016 | 6 | 2016 | 6 | 2016 |
6219081 | 01.07.2016 | 26 | 2016 | 52 | 2016 | 52 | 2016 | 52 | 2016 |
5800726 | 29.12.2015 | 53 | 2015 | 26 | 2016 | 26 | 2016 | 26 | 2016 |
6081490 | 20.04.2016 | 16 | 2016 | 36 | 2016 | 29 | 2016 | 29 | 2016 |
6059065 | 12.04.2016 | 15 | 2016 | 44 | 2016 | 44 | 2016 | 44 | 2016 |
5670430 | 30.10.2015 | 44 | 2015 | 13 | 2016 | 13 | 2016 | 13 | 2016 |
5581594 | 22.09.2015 | 39 | 2015 | 24 | 2016 | 24 | 2016 | 24 | 2016 |
5511855 | 05.10.2015 | 41 | 2015 | 14 | 2016 | 14 | 2016 | 14 | 2016 |
5507543 | 16.10.2015 | 42 | 2015 | 22 | 2016 | 20 | 2016 | 12 | 2016 |
5271805 | 30.06.2015 | 27 | 2015 | 4 | 2016 | 4 | 2016 | 4 | 2016 |
7050217 | 25.06.2017 | 25 | 2017 | 3 | 2018 | 3 | 2018 | 2 | 2018 |
6020281 | 02.06.2016 | 22 | 2016 | 41 | 2016 | 41 | 2016 | 41 | 2016 |
Solved! Go to Solution.
Almost correct
this is the correct formula that works
Column =
VAR __dec31 = DATE([Req. Del. Year],12,31)
VAR __weeks = [Req. Del. Week]-[Creation Week]
RETURN
IF(
[Creation Week] > [Req. Del. Week],
WEEKNUM(__dec31) -[Creation Week]+ [Req. Del. Week],
__weeks
)
but thank you very much without you I would not able to finish it..
Perhaps:
Column = VAR __weeks = [Creation Week] - [Req. Del. Week] RETURN IF( [Req. Del. Week] > [Creation Week], 52 + __weeks + [Creation Week], __weeks )
ok thats a good start but how to deal with the fact that each year has a different number of weeks..
Try this:
Column =
VAR __dec31 = DATE([Req. Del. Year],12,31) VAR __weeks = [Creation Week] - [Req. Del. Week] RETURN IF( [Req. Del. Week] > [Creation Week], WEEKNUM(__dec31) + __weeks + [Creation Week], __weeks )
Almost correct
this is the correct formula that works
Column =
VAR __dec31 = DATE([Req. Del. Year],12,31)
VAR __weeks = [Req. Del. Week]-[Creation Week]
RETURN
IF(
[Creation Week] > [Req. Del. Week],
WEEKNUM(__dec31) -[Creation Week]+ [Req. Del. Week],
__weeks
)
but thank you very much without you I would not able to finish it..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |