Forum Discussion
Weekdays calculation from slicer
Hi There
I got a date slicer and from that I am trying to calculate number of weekdays. Thus, first I created a column:
IsWorkday = SWITCH(WEEKDAY('VW_RPT'[Start_Date]),1, 0, 7,0,1)
Then, created a meassure
Business Days =
CALCULATE(
SUM('VW_RPT'[IsWorkday])
,ALLSELECTED('VW_RPT'[Start_Date])
)
If I select July 1, 2019 to July 31, 2019, it calculates Business Days = 1063 days. Surely, not the correct number of weekdays.
Can you point me out, what am I doing wrong?
Michael.
So you probably could still get this to work in this case, it will just take a different approach. I think a pattern like the following might work:
CALCULATE( COUNTROWS( VALUES( PBI[Start_Date] )), PBI[IsWorkDay] = 1 )
15 Replies
Thanks, I am new to Power BI, your link looks good but surely looks much more complex and I do not need that much option. I only have one date filed and I needed to have something simpler like this. Can you suggest why mine is not producing the right result? Probably that will help me understand your link.
Currenly I am confused with NetWorkDays from your link, it looks like it is a meassure and also a table.
Correct, because you are using a slicer and the date range is interactive, you need a measure. The basic concept of the measure is to create a table using CALENDAR that spans your selected date range and store this in a variable. You add a column to this table that is the weekday. You filter the table to remove workdays you don't want and return the count of the rows. That's pretty much the long and the short of it.
What is the granularity of the vw_rpt table? Do you have a single row per date or multiple rows per date? Typically if I am doing work day calculations I will use a separate calendar table in my model that has a continuous set of dates and only one row per date.
