Date > today () -15 Working days

[Karlos]

I'm trying to get a calculated column that returns a true or false if a certain date falls within 15 working days. 

I have 2 tables, Table 1 and a date table. 

Table 1's calculated column = 

Measure 1 = 
IF (
    DATEVALUE ( 
'Table 1'[PO Date] )        > ( TODAY () - 15 ),
    TRUE (),
    FALSE())

^ this only looks at the last 15 days but I need working days. 

My date table is linked and and contains a list of all working/ non working days. 

So I need help amending the code, so it returns a True if it's over 15 working days or False if it's within 15 working days. 

[CNENFRNL · Accepted solution]

@Karlos ,

It seems only weekdays, i.e. Mon-Fri, are defined as working days in your model, without taking into consideration any holidays. If so, you even don't bother to use a calendar table.

=
TODAY () - 21 <= [PO Date] && [PO DATE] < TODAY ()
    && WEEKDAY ( [PO DATE], 2 ) < 6
//whatever day is today, previous 21 days surely contain exact 15 working days

[amitchandak]

@Karlos , Have these column in your date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

Last 15 work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-15))

refer

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Karlos]

Hi @amitchandak , 

I can't seem to get this to work for me. I've created a test document for you so you can see what I'm working with. 

https://1drv.ms/u/s!AnIEh6WhI4Jogv5SHwsMx0mFuwM7iQ?e=0ZWXNg 

Essentially, I just need a way to calculate the date of "PO Date" - "15 Working days"

So for example 
PO DATE             PO - 15 working days
27/11/2020         06/11/2020

 
Thanks in Advance. 
Karlos. .