Number of Days Calculation

[Matt_Mohawk]

I am trying to figure out an issue with a formula that I have created. I am needing to modify this formula to add an extra day to the "DATE IN" field but ignore dates that have the same "DATE IN" and "DATE OUT". Here is the formula that I am currently using:

# of Days =
VAR DateOutBlank = IF(ISBLANK(MJS_PRICE_PW[DATE OUT]),0,MJS_PRICE_PW[DATE OUT])
RETURN CALCULATE(SUM('Date'[Workday Not Holiday]),FILTER(MJS_PRICE_PW,IF(MJS_PRICE_PW[DATE IN]=MJS_PRICE_PW[DATE OUT],1)),DATESBETWEEN('Date'[Date],MJS_PRICE_PW[DATE IN],DateOutBlank))

[Matt_Mohawk]

@amitchandak thank you for that formula. I am getting the error, "The start date in Calendar function can not be later than the end date."

I am working with some projects that do not have a "DATE OUT" entered yet. Could this be the cause of that problem? That is the main reason why I had the VAR DATEOUTBLANK in my formula before.

[amitchandak]

@Matt_Mohawk , add an if on top of it and check if it works. or share small sample with expected output

[Matt_Mohawk]

Below is an example of what I am needing. The areas marked in red show that right now it is counting as 2 days, this is actually supposed to be 1 day because we do not count the day that it comes into us. The green area is the area that I want the formula to ignore because if a project comes in and goes out on the same day, it needs to be counted as 1 day. Hope this makes sense.

[v-yiruan-msft · Accepted solution]

Hi @Matt_Mohawk ,

Please try to update the formula of your calculated column [# of days] as below:

# of Days = 
VAR _days =
    CALCULATE (
        SUM ( 'Date'[Workday Not Holiday] ),
        DATESBETWEEN ( 'Date'[Date], MJS_PRICE_PW[DATE IN], MJS_PRICE_PW[DATE OUT] )
    )
RETURN
    IF (
        ISBLANK ( MJS_PRICE_PW[DATE OUT] ),
        BLANK (),
        IF (
            MJS_PRICE_PW[DATE IN] = MJS_PRICE_PW[DATE OUT],
            1,
            _days
                - IF ( NOT ( WEEKDAY ( MJS_PRICE_PW[DATE IN], 1 ) IN { 1, 7 } ), 1, 0 )
        )
    )

Best Regards

[Matt_Mohawk]

@v-yiruan-msft That did it! You are a lifesaver! Now I only have to figure out how you got it to work so I can learn to do this for later jobs.

[Matt_Mohawk]

Hi @amitchandak,

I was wondering if you have any updates for me?

[amitchandak]

@Matt_Mohawk , You can working days like

example measure 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

example column

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

Also check 2nd page for workday date diff attcahed file using date table