cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Date > today () -15 Working days

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. 

Karlos_0-1606495280739.png

 

Karlos_0-1606495478830.png

 

 

 

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. 

 

 



1 ACCEPTED SOLUTION
Super User III
Super User III

@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

 

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

@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

 

 

View solution in original post

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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. .

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors