Calculate number of weekends (Fridays and Saturdays) between two dates

[goodhvnting]

Hello!

I've been searching this community for a solution but I don't believe this has been answered in my specific scenario. Hoping someone can help me with the following problem:

I want to calculate the number of weekend nights (Friday and Saturdays) between two dates. I have the following columns:

PERMIT_START_DT
PERMIT_END_DT

I'd like to create a new column (not measure) that tells me how many weekend nights are between those two date columns. 

Much appreciated! 

[davehus]

Hi @goodhvnting ,

Are you happy for the column to only update on refresh as this will be the case with a calculated column?

[goodhvnting]

Yes, that works for me. Thank you! 

[davehus]

This is with DAX. You can do it with powerquery either. Swap Date() for your Permit_start

IsWeekend = IF(AND(OR(WEEKDAY('Date Table'[DateKey])=7,WEEKDAY('Date Table'[DateKey])=1),AND('Date Table'[DateKey]>date(2021,01,01),'Date Table'[DateKey]<DATE(2021,01,31))),1,0)

Did I help you today? Please accept my solution and hit the Kudos button.

[goodhvnting]

Thanks for the response. 

Finding this solution a little confusing... can you confirm that the DAX formula is used in the "New Column" function? 

Where would the PERMIT_START_DT and PERMIT_END_DT columns be used? These columns are in a sepearate table not in a "Date Table".

Appreciate your time. 

[davehus]

Hi @goodhvnting , Yes it is. So the calculation is checking if the weekday is a Friday OR Saturday and the dates are between the start AND end date.  I've modified and test with a separate table and it works.

IsWeekend =
IF (
    AND (
        OR (
            WEEKDAY ( 'Date Table'[DateKey] ) = 6,
            WEEKDAY ( 'Date Table'[DateKey] ) = 7// Is it Friday or Saturday
        ),
        AND (
            'Date Table'[DateKey] > MAX ( Parameter[PERMIT_START_DT] ),
            'Date Table'[DateKey] < MAX ( Parameter[PERMIT_END_DT] )
        )
    ),
    1,
    0
)

[goodhvnting]

Thanks again for your response. 

Apologies, my question may not have been very clear. 

I am trying to count the number of Fridays and Saturdays that occur between two dates. Not if it is a Friday or Saturday. 

Example:  

PERMIT_START_DT	PERMIT_END_DT
Friday, August 23, 2019	Sunday, August 25, 2019
Thursday, August 01, 2018	Friday, August 03 2018
Friday, August 22, 2014	Sunday, August 24, 2014

I want to create a new column using a DAX formula that will count the total number of Fridays AND saturdays between those 2 dates. It should look something like this: 

Weekend_Nights

2

1

2

Thanks again for your time and consideration. Truly appreciate it. 

[goodhvnting · Accepted solution]

Somehow I was able to solve my own question! Thank you to davehus for trying to help me. 

This is the formula I used to get the answer to my question: 

Weekend_Nights = COUNTROWS(FILTER(CALENDAR(RAP_Permits_to_Postal_Code[PERMIT_START_DT],RAP_Permits_to_Postal_Code[PERMIT_END_DT]), WEEKDAY([Date]) in {6,7} ))

[davehus]

Hi @goodhvnting , 

You would have got the same result once the columnn was dragged into the canvas. See below.

Glad you got sorted.