Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. 

 

 

 

 

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's avatar
    CNENFRNL
    5 years ago

    Anonymous ,

     

    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

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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-Calendar-4-5-Power/ba-p/1187766

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Anonymous ,

     

    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