Forum Discussion

aktripathi2506's avatar
aktripathi2506
Icon for Helper IV rankHelper IV
10 years ago
Solved

Calculating number of weeks between two dates from different year

Hi,

 

 

 

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

 

Drug NameExp
Alp 32002-12-2018
B02-10-2017
C12-04-2016
D22-04-2010
E03-07-2020
F23-05-2017
G24-05-2016
H25-05-2016
I26-05-2018
J27-05-2016
K27-05-2019
L29-05-2011
M30-05-2012
N03-01-2010
O03-06-2016
P02-06-2017

 

 

Exp. date is of different year.

 

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

 

Current DateWeek number
14-08-201634
15-08-201634
16-08-201634
17-08-201634
18-08-201634
19-08-201634
20-08-201634
21-08-201635
22-08-201635
23-08-201635
24-08-201635
25-08-201635
26-08-201635
27-08-201635
28-08-201636
29-08-201636
30-08-201636
31-08-201636
01-09-201636
02-09-201636
03-09-201636
04-09-201637

 

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.

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      aktripathi2506
      Icon for Helper IV rankHelper IV
      10 years ago

      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's avatar
        aktripathi2506
        Icon for Helper IV rankHelper IV
        10 years ago

        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.