Calculating number of weeks between two dates from different year

[aktripathi2506]

Hi,

Situation is like this: Suppose i Have a drug data with its expiry date:

Drug Name	Exp
Alp 320	02-12-2018
B	02-10-2017
C	12-04-2016
D	22-04-2010
E	03-07-2020
F	23-05-2017
G	24-05-2016
H	25-05-2016
I	26-05-2018
J	27-05-2016
K	27-05-2019
L	29-05-2011
M	30-05-2012
N	03-01-2010
O	03-06-2016
P	02-06-2017

Exp. date is of different year.

Now I have another column with date, based on which I calculated the week number.

Current Date	Week number
14-08-2016	34
15-08-2016	34
16-08-2016	34
17-08-2016	34
18-08-2016	34
19-08-2016	34
20-08-2016	34
21-08-2016	35
22-08-2016	35
23-08-2016	35
24-08-2016	35
25-08-2016	35
26-08-2016	35
27-08-2016	35
28-08-2016	36
29-08-2016	36
30-08-2016	36
31-08-2016	36
01-09-2016	36
02-09-2016	36
03-09-2016	36
04-09-2016	37

Now I have 2 slicer from this: Year and Weeknum.

When I select year as 2016 and week as 34 then I should get the name of drugs with their week to expiry data. (how many weeks remaining to expire the medicine)

I tried:  weeknum(exp) - weeknum(current date)

for same year (exp year = 2016) it is working fine for other years it is not working fine.

I am trying for a dynamic report where i can go to past or futer and figure out at tht time how many weeks were remaining for the drug to expire.

Meaning based on week slicer I should be able to see the data according to the week selected.

Thanks.. Please advise.

[Greg_Deckler]

In general, I would combine your year with this, so something like:

YearAndWeek = YEAR([Exp]) * 100 + WEEKNUM([Exp])

Then you could calculate today as:

Today = YEAR(TODAY()) * 100 + WEEKNUM(TODAY())

Then you could go backwards and forwards and account for the year.

[aktripathi2506]

Thank you @Greg_Deckler

But its not working

Suppose the exp date is 1-1-2017 then according to the suggested formula it will be

201700 + 1 = 201701

if I select week as 53 for the year 2016 than

201701 - 201653 = 48

Which is not correct.

[aktripathi2506]

Just a thought @Greg_Deckler, 

Is there any thing that we can fix the reference year say 2000 or 1900 and then we calculate the week number for the years and subtract them.

Suppose then for date 2 july 2017 the week number comes as 750 and for the date 2 sept 2016 date comes as 700 and then we take the difference = 50 weeks to go.

[v-yuezhe-msft · Accepted solution]

Hi @aktripathi2506,

To calculate number of weeks between two dates, you can firstly calculate days between dates using formula likes below:

DaysNo = IF(Table6[Exp]<Table6[Current Date],DATEDIFF(Table6[Exp],Table6[Current Date],DAY),1*DATEDIFF(Table6[Current Date],Table6[Exp],DAY))

Then you are able to calculate weeks between dates by using the following formula.

Weeknum = ROUNDDOWN((Table6[DaysNo] / 7), 0)

There is an example for your reference, and you can review the example in this attached PBIX file.



Thanks,
Lydia Zhang

[Anonymous]

Hey Hi,

This one best fits my need. 

But Can i ask for one more thing. There are cases where my no.of.days are even lesser than 7days. 

Which techincally means that the Week numbers to be 0. But is there a way i can set a condition for which the days no < 7 returns me 1. 

Thanks,

Govind

[Anonymous]

In addition you can directly calculate the difference in WEEK by setting WEEK at the end of DATEDIFF function !

[aktripathi2506]

Thank you @v-yuezhe-msft

It worked perfect, thats what I was looking for, Thank you.

Cheers!!