locked
Getting number of weekdays per month RRS feed

  • Question

  • Currently I've got a column and respective visual in pbi where I look at an employees tasks during the week and decide their overnight stays (when they are working offsite). The table where I am doing this contains one row for each task the employee does. 

    The visual is the percentage of days the worker spends overnight and I am currently just dividing the total overnight stays by 260 which is approximately the number of weekdays in a year. My issue is that I have a month slicer in the power bi report that doesn't allow the other visual to work because it then just divides the overnight stays by that particular months overnight stays.

    So for example if a worker has 12 overnight stays in a month it currently divides 12/260, but I need it to divide by the number of weekdays in that particular month instead. For April 2019 it would be 12/22.

    I'm not really sure how to go about doing this because I don't have each day as a row in the table, but only the rows for which employee has tasks. 

    Any help would be appreciated.

    Wednesday, May 8, 2019 6:11 PM

Answers

  • Hi jshinnenkamp,

    Here is the solution for your reference.

    Sample table Overnight

    Dax for calculated table "sumOvernight"

    =SUMMARIZE(filter(Overnight,Overnight[Stayed Overnight]=1),Overnight[Name],Overnight[Month],"WeekdaySpent",count(Overnight[Stayed Overnight]))

    Dax for calculated table "Calendar"

    =CALENDAR(min(Overnight[TimesheetDate])-day(min(Overnight[TimesheetDate]))+1,EOMONTH(max(Overnight[TimesheetDate]),0))

    Dax for calculated table "sumCalendar"

    =SUMMARIZE(filter('Calendar','Calendar'[Weekday]<>1 && 'Calendar'[Weekday]<>7),'Calendar'[Month year],"sumWeekdays",count('Calendar'[Date]))

    Dax for calculated table "CalculatedTable 1"

    =SELECTCOLUMNS(FILTER(CROSSJOIN(sumOvernight,sumCalendar),sumOvernight[Month]=sumCalendar[Month year]),"Name",sumOvernight[Name],"Month Year",sumOvernight[Month],"WeekdaysSpent",sumOvernight[WeekdaySpent],"TotalWeekdays of Month",sumCalendar[sumWeekdays],"%of weekdays Spent Overnight",ROUNDDOWN(DIVIDE(sumOvernight[WeekdaySpent],sumCalendar[sumWeekdays]),2))

    And this is the final output

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jshinnenkamp Friday, May 10, 2019 2:15 PM
    Friday, May 10, 2019 10:43 AM

All replies

  • Hi jshinnenkamp,

    Thanks for your long and meaningful description.

    However, just abstract description doesn't help solve your problem effectively. Could you please show us your fact table with some sample data? Then provide a specific format of your desired output. With these key information, we could exactly know your requirement.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 9, 2019 5:48 AM
  • So here is some sample data where I have it filtered to the worker Adam and the Month April 2018. I've got logic already figured out to decide if the employee stayed overnight as you can see in the final column there. As you can see Adam had 12 overnight stays this month, but as you can see the measuer that is outlines still just divides this by 260. I need it to divide this by the amount of Weekdays for that month (or whatever time frame is selected). For this instance it would be 12/21. 

    Basically this sample data is how the table is set up where there is a row for each task for each worker. 

    So I guess the issue is, I'm not really sure how to get my measure to work properly because I'm not sure how to figure out the proper amount of weekdays when each date isn't already included in the table.

    Thanks.

    Thursday, May 9, 2019 3:23 PM
  • You can create a dim table with month and workdays. Joining your table with the dim table on Month=Month and so you can pickup the workdays fopr that month. In PBI you can load the view (your Table + dim table) instead of your table. ANd starting computing f the view instead of the table. 

    Please mark as answer if this post helped you

    Thursday, May 9, 2019 3:43 PM
  • What would be the best way to accomplish this in my model? 

    FILTER(CROSSJOIN('CalculatedTable 2', Overnight), Overnight[Months]='CalculatedTable 2'[MonthYear])

    (CalculatedTable 2 is the new table of dates and weekdays, Overnight is my other table)

    I'm trying to do something like this but it's returning each of the 14 rows for Adam for each day in April, so I'm assuming a I need a better filter. This is my result, using Adam and April as a example again:

    I would hope that the Overnight part of the crossjoin would be blank if Adam for example didn't have a task on 4/1. In doing so however I would know that it was a weekday so my measure would work properly. 


    Thursday, May 9, 2019 7:01 PM
  • Hi jshinnenkamp,

    Here is the solution for your reference.

    Sample table Overnight

    Dax for calculated table "sumOvernight"

    =SUMMARIZE(filter(Overnight,Overnight[Stayed Overnight]=1),Overnight[Name],Overnight[Month],"WeekdaySpent",count(Overnight[Stayed Overnight]))

    Dax for calculated table "Calendar"

    =CALENDAR(min(Overnight[TimesheetDate])-day(min(Overnight[TimesheetDate]))+1,EOMONTH(max(Overnight[TimesheetDate]),0))

    Dax for calculated table "sumCalendar"

    =SUMMARIZE(filter('Calendar','Calendar'[Weekday]<>1 && 'Calendar'[Weekday]<>7),'Calendar'[Month year],"sumWeekdays",count('Calendar'[Date]))

    Dax for calculated table "CalculatedTable 1"

    =SELECTCOLUMNS(FILTER(CROSSJOIN(sumOvernight,sumCalendar),sumOvernight[Month]=sumCalendar[Month year]),"Name",sumOvernight[Name],"Month Year",sumOvernight[Month],"WeekdaysSpent",sumOvernight[WeekdaySpent],"TotalWeekdays of Month",sumCalendar[sumWeekdays],"%of weekdays Spent Overnight",ROUNDDOWN(DIVIDE(sumOvernight[WeekdaySpent],sumCalendar[sumWeekdays]),2))

    And this is the final output

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jshinnenkamp Friday, May 10, 2019 2:15 PM
    Friday, May 10, 2019 10:43 AM