Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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} ))
Hi @goodhvnting ,
Are you happy for the column to only update on refresh as this will be the case with a calculated column?
Yes, that works for me. Thank you!
This is with DAX. You can do it with powerquery either. Swap Date() for your Permit_start
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.
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
)
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.
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} ))
Hi @goodhvnting ,
You would have got the same result once the columnn was dragged into the canvas. See below.
Glad you got sorted.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
38 | |
30 |