difference between two dates in weeks

[Anonymous]

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

[Greg_Deckler]

Perhaps:

Column = 
VAR __weeks = [Creation Week] - [Req. Del. Week]
RETURN  
  IF(
    [Req. Del. Week] > [Creation Week],
    52 + __weeks + [Creation Week],
    __weeks
  ) 

[Anonymous]

ok thats a good start but how to deal with the fact that each year has a different number of weeks.. 

[Greg_Deckler]

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
  ) 

[Anonymous · Accepted solution]

@Greg_Deckler

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