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.
I am trying to determine the number of business / workdays in the current month & the previous month.
I have a "Production" Fact Table on my dashboard with a period of 2 years.
I would also like to be able to do the following :
1) Determine the number of business / workdays in the current month & the previous month.
2) Allow the number of business / workdays in the current month & the previous month to change according to the selected "Production" date. (not compulsary)
3) Incorporate manual input of holidays possibly in sharepoint (yet to decide how) into the calculation of number of business / workdays in the current month & the previous month based on selected date.
What i have done so far :
Solved! Go to Solution.
H @Anonymous
With a date table that looks something like this (and make sure it is marked as a date table!)
Create these measures
Workdays =
COUNTROWS(
FILTER(
'Calendar',
AND(
'Calendar'[Weekday] <> 1,
'Calendar'[Weekday] <> 7
)
)
)
Workdays PM =
CALCULATE(
[Workdays],
PREVIOUSMONTH('Calendar'[Date])
)
@Anonymous , New column in date table
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
as column
Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
Work day of previous month = Sumx(filter(Date, eomonth([Date],0) = eomonth(earlier([Date]),-1) ),[Work Day])
Measure
MTD WorkDay= CALCULATE(SUM('Date'[Work Day]),DATESMTD('Date'[Date]))
last MTD WorkDay= CALCULATE(SUM('Date'[Work Day]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
H @Anonymous
With a date table that looks something like this (and make sure it is marked as a date table!)
Create these measures
Workdays =
COUNTROWS(
FILTER(
'Calendar',
AND(
'Calendar'[Weekday] <> 1,
'Calendar'[Weekday] <> 7
)
)
)
Workdays PM =
CALCULATE(
[Workdays],
PREVIOUSMONTH('Calendar'[Date])
)
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |