Forum Discussion

koshur's avatar
koshur
Icon for Helper I rankHelper I
9 years ago

Contract duration minus won date

I have a column for Bid won date and then a column with contract duration in months. I want to show all the bids since 2013 that would be due for renewal in comming years. How to go about it 

 

Column A = Won date (01/01/2017) - formatted as date

Comun X = Contract Duration (36) -- formated as number  

 

How do i normalized and calculate the count of bids to be renewed. Every bid has a unique key in a column (say B).

 

Kindly advise.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi koshur,

    I am not quite sure about your logic, please create the following measure in your table and check if it returns your expected result.

    Count = CALCULATE(COUNTA(Table[Column B]),FILTER(Table, Table[Column X]<= DATEDIFF(DATE(2013,1,1),Table[Column A],MONTH)&& DATEDIFF(DATE(2013,12,31),Table[Column A],MONTH)<=Table[Column X]))

    If the above formula doesn't help you, please help to post whole sample data of your table and desired result.

    Thanks,
    Lydia Zhang

    • koshur's avatar
      koshur
      Icon for Helper I rankHelper I
      9 years ago

      I create a calculte column finally to find the date difference to populate the renewal date. :)