DDLINE CHECKING

[Anonymous]

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)

OUTPUT TABLE: (get the DDLINE based on the calendar table above that has no weekends and holidays)

Thank you so much for your help! Rally appreciate it 🙂 

[Anonymous · Accepted solution]

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

[amitchandak]

@Anonymous , if you want to add working days, refer to my blog

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

[Anonymous]

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]

@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]

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  🙂