Count week days to calculate delivery time

[carlosagarcia]

Hello guys.

I am trying to create a meassure that only counts week days to calculate the delivery for an item.

I have a purchasing lead time of 10 days, if the order is entered today 06/12/2020, the estimated time of delivery should be 06/25/2020

Part     Lead time    Delivery date

123          10                 ??? 

I know it sounds easy, I have tried different methods with no luck.

[v-eachen-msft · Accepted solution]

Hi @carlosagarcia ,

You need to create a date dimension.

Date =
CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )

Then create columns to show working days and running total. 

Count =
VAR a = WEEKDAY ( 'Date'[Date], 2 ) RETURN IF ( a < 6, 1, 0 )

Sum =
CALCULATE (
    SUM ( 'Date'[Count] ),
    FILTER ( 'Date', 'Date'[Date] <= EARLIER ( 'Date'[Date] ) )
)

Create a measure to show date.

Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Date] )
VAR b =
    SELECTEDVALUE ( 'Table'[Days] )
VAR c =
    CALCULATE ( MAX ( 'Date'[Sum] ), FILTER ( 'Date', 'Date'[Date] = a ) )
RETURN
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER ( 'Date', 'Date'[Sum] = b + c - 1 && 'Date'[Count] = 1 )
    )

Here is my test file for your reference.

[carlosagarcia]

Hello, 

I am ready to test your answer but I get stuck on the Messure column.

I get an error on 

Measure =

VAR a =

SELECTEDVALUE ( 'Table'[Date] ),  I need this section to be today ot tomorrows date.

I have no date to go by, I am just assuming that if the parts are ordered today or tomorrow, what the expected date should be.

I tried creating a column with todays date but the messure doesnt see it.

Any ideas?

[v-eachen-msft]

Hi @carlosagarcia ,

Change it to be TODAY() or TODAY()+1.

var a = TODAY()

Make sure that the date dimension have all dates you need in the future.

[carlosagarcia]

Thank you, worked like a charm. 

[amitchandak]

@carlosagarcia , Have a date table with following field

Table
Date = CALENDAR(date(2018,01,01), date(2021,12,31)) 
Columns
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)

Use Rank to Travel . Example Measure
Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
return CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date  Rank] =_max+10))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.