Weekdays calculation from slicer

[Anonymous]

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.

[Greg_Deckler]

See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[d_gosbell]

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.

[Anonymous]

multiple rows per date i.e. same date appears in multiple rows. I think you kind of pointed me to the the right direction, I guess now I am calulating sum of many.

[d_gosbell · Accepted solution]

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 )

[Anonymous]

It works! Thank You

[Anonymous]

Hi All

I got a related question. The above porposed solution is still working. But given that there are some dates are missing in the database, thus, the calculation often returning wrong results if a date from weekdays is missing. 

For example, November 1, 2019 to November 30, 2019, it should say, total working days is 21. Instaed in my case it is showing 19. This is because, in my data I am missing data for two days those belong to weekdays.

Can you help me get the actual weekdays based on my slicer selection, i.e. independent of data?

[d_gosbell]

---

@Anonymous wrote:

I got a related question. The above porposed solution is still working. But given that there are some dates are missing in the database, thus, the calculation often returning wrong results if a date from weekdays is missing. 

---

So the best practice here is to create a separate date table which is then related to the date column in your transaction table. Your date table should then contain a continuous range of dates with no missing values. Then you will not have this problem.

[Anonymous]

Total Work Days =
VAR Date_Selected_Min =
CALCULATE ( MIN ( 'Table'[Start_Date] ), ALLSELECTED ( 'Table'[Start_Date] ) )
VAR Date_Selected_Max =
CALCULATE ( MAX ( 'Table'[Start_Date] ), ALLSELECTED ( 'Table'[Start_Date] ) )
RETURN

DATESBETWEEN('Table'[Start_Date]
Date_Selected_Min,
Date_Selected_Max
)

Thanks again, and that is something I am trying with the above code. But I cannot figure out how to use the return value from this. In fact I cannot see what is the outcome of this, though it is logically make sense to me. Any help with code?

[d_gosbell]

---

@Anonymous wrote:

Thanks again, and that is something I am trying with the above code. But I cannot figure out how to use the return value from this. In fact I cannot see what is the outcome of this, though it is logically make sense to me. Any help with code?

---

No, I'm not talking about doing this in code. I'm talking about adding a table to your data model (You could do this using the CALENDAR() or CALENDARAUTO() functions in a calculated table). The DATESBETWEEN function above is still only going to return values that exist in the specified column so you will hit the same issue as before.

[Anonymous]

Sorry, my lack of experience probably talking.

I used the menu to create a table as DateTable = CALENDARAUTO() . Then I created the slicer using the Date column from this new table.

Then I thought to change IsWorkday = SWITCH(WEEKDAY(DateTable[Date]),1, 0, 7,0,1) i.e. in reference to the new table. But it does not recognize the new table.

Can you give me more step by step instructions? 

[d_gosbell]

---

@Anonymous wrote:

But it does not recognize the new table.

I don't know what you mean by the statement above so it's hard to say what is going wrong. To calculate the number of business days you should not need any other tables. If you have existing measures that you want to be affected by this new slicer you would need to create a relationship between the date column in this new table and the date column in your original table.

[Anonymous]

days you should not need any other tables

Right. Your previous solution with 

IsWorkday = SWITCH(WEEKDAY('TABLE'[Start_Date]),1, 0, 7,0,1)

Business Days = CALCULATE( COUNTROWS( VALUES( TABLE[Start_Date] )), TABLE[IsWorkDay] = 1 )

is still working. But for November 2019 it is showing  me that business day is 19, which is wrong. It should be 21. This is becuase in my data the Start_Date does not have data for 2 days. Thus, I need another table which will have all the date, for me to calculate the Business Days.

relationship between the date

Yes, I created relationship between Data column from the new table to the Start_Date column of the old table. But again, given that I do not have data for 2 days in my data, Business Days is still showing 19.

How can I show that Business Days in November 2019 is actully 21 not 19? That I need to do independent of my underlying data which is missing data for 2 days.

[d_gosbell]

---

@Anonymous wrote:

IsWorkday = SWITCH(WEEKDAY('TABLE'[Start_Date]),1, 0, 7,0,1)

Business Days = CALCULATE( COUNTROWS( VALUES( TABLE[Start_Date] )), TABLE[IsWorkDay] = 1 )

---

The above two calcs still appear to be referencing your original table. If you've created a new table called DateTable you need to create a new calculated column in DateTable with a formula of:

IsWorkday = SWITCH(WEEKDAY('DateTable'[Date]),1, 0, 7,0,1)

And then your business days measure would become:

Business Days = CALCULATE( COUNTROWS( VALUES( DateTable[Date] )), DateTable[IsWorkDay] = 1 )

[Anonymous]

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.

[Greg_Deckler]

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.