Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DDLINE CHECKING

Newbie here,   Is there any way to get the DDLINE based on a filtered calendar table.   For example:   CALENDAR TABLE (filtered all the weekends and holidays)                   ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You could add a new index column for the calendar table.

    Then create below formulas.

    Column = LOOKUPVALUE('CALENDAR'[Index2],'CALENDAR'[date],OUTPUT[date])+OUTPUT[duration]

Column 2 = CALCULATE(MAX('CALENDAR'[date]),FILTER(ALL('CALENDAR'),'CALENDAR'[Index2] = OUTPUT[Column]))

     

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
5 years ago


Hi amitchandak ,

 

Thank you for your insights. However, i don't like to add working days. I just wanted to count the days starting from ENDORSED DATE of the project until it's ddline based on DURATION column . but i want to count it based on another CALENDAR TABLE that has no weekends and holidays . so if you can see the orange table above . that is the calendar table that has already no weekends and holidays and I've filtered it already in Power Query.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , A new column in the second table

    countx(filter(CALENDAR, CALENDAR[Date] >=Table[Date] && CALENDAR[Date] <=Table[DEDLINE]),CALENDAR[Date])

     

    In case the first table has a flag for the workday add that to calculation

     

     

    Second page of attached file shows measure way

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      Thank you again for your input. However, the output that i am looking for is not the same output from your query.

       

      What I want is if a project is given in blue table for example project 1, it was ENDORSED on 4/2/2021, there is a given period of days for it to be finished, that is the DURATION column, so the project 1 should be finished 5 days after the ENDORSED DATE. What I want is to be able to get the DEADLINE on the project based on the DURATION given but I would like to exclude the weekends and holidays. Thats why I have the ORANGE TABLE (Calendar Table) that has no weekends and holidays already. I just wanted the DATE that the project should be finished based on the CALENDAR TABLE (Orange table above).

       

      Thank you so much for your patient and inputs amitchandak  🙂