Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
eed a help to create networking days in each month .
I have master table in which a column called "workdate". work date starts from 01-01-2021 to 12-31-2021.
I have another table which having the holidays details. i need to find the networking days of each month considering weekends and holidays. please help me.
New Year's Day | Jan 01, 2021 (Fri) |
Republic Day | Jan 26, 2021 (Tue) |
Ramzan Id/Eid-ul-Fitar | May 14, 2021 (Fri) |
Diwali / Deepavali | Nov 04, 2021 (Thu) |
Christmas Eve | Dec 24, 2021 (Fri) |
Solved! Go to Solution.
You will have to create a relationship between the 2 tables and create a measure to count the working days:
WorkingDaysCount =
// Assuming weekend is Sat & Sun .... adjust if required
CALCULATE (
COUNTROWS ( 'WorkDate' ),
FILTER (
'WorkDate',
NOT ( 'WorkDate'[WeekDay] IN { "Sat", "Sun" } )
&& RELATED ( Holidays[Date] ) = BLANK ()
)
)
Please check the sample file.
Here if i have city also in holidays table as you have written.. how will i consider that??
hi,
how i can calculate YTD utilization from this data?
i used this formula
DATESYTD has to be used on a proper calendar table and the YTD calculation formula is:
YTD Value =
CALCULATE ( [Measure], DATESYTD ( 'CalendarTable'[Date] ) )
So, create a proper measure then apply the same syntax on a Calendar table.
It is very important to have a separate Calendar table in your model if you are working with dates.
In Power Bi Desktop, Modeling, New Table
Calendar = CALENDAR( DATE ( 2015,1,1 ), Date ( 2025,12,31 ) )
// You can customize the start and end date
You will have to create a relationship between the 2 tables and create a measure to count the working days:
WorkingDaysCount =
// Assuming weekend is Sat & Sun .... adjust if required
CALCULATE (
COUNTROWS ( 'WorkDate' ),
FILTER (
'WorkDate',
NOT ( 'WorkDate'[WeekDay] IN { "Sat", "Sun" } )
&& RELATED ( Holidays[Date] ) = BLANK ()
)
)
Please check the sample file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |