Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm looking after counting working days function. However, not for historical months (like February in the example attached) but also for current month where my latest and greatest date in the system is up until today only (March 4th).
In other words I'm searching for a formula that will extract the month we are in based on MMMM and YYYY from my [insert_time] table and will tell me how many working days are expected to be in this month.
I can not add in date table as I'm sourcing SQL database via a DirectQuery mode. So I have some limitations there.
Thanks,
NH
Solved! Go to Solution.
@nhol- Try this, it takes a single input date (in the example, Calendar[Date]) and returns working days in the month.
NetWorkDays = VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1) VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1 VAR Calendar1 = CALENDAR(StartDate,EndDate) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
I have a Quick Measure in the Gallery for that. It adds a Calendar table dynamically.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
Hi @nhol
Try this MEASURE
CountWorkingDays = VAR Temptable = ADDCOLUMNS ( VALUES ( TableName[insert_time] ), "WeekDay", WEEKDAY ( TableName[insert_time], 2 ) ) RETURN COUNTROWS ( FILTER ( Temptable, [WeekDay] <= 5 ) )
Hi,
This formula provide me with "1" for each row that has a stamp date. I need a formula that based on the date, assuming March 5th 2018, it will know that we are in the month of March (2018) and it will provide me with 22 working days eventhough I don't have a full month dates in teh system.
Thanks!
NH
@nhol- Try this, it takes a single input date (in the example, Calendar[Date]) and returns working days in the month.
NetWorkDays = VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1) VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1 VAR Calendar1 = CALENDAR(StartDate,EndDate) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
Brilliant!!!
Thank you so much!
Nir
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |