How to create a view to Calculate count of employees by Weekdays for a selected period

[toovishant]

Hi Team,

Need to create a view that determines how many days in a week the employees prefer to come office. 

Below is the sample data set.

https://docs.google.com/spreadsheets/d/16eDvf4QESH_gDhw4tk_tYSEnZ77ORf2Y/edit?usp=sharing&ouid=10867...

Required View 

For below Ex:  Total employees 110,650 came to the office for the given period and below are the number of days in a week they prefer to come to office. The below view shows most of the employees for the selected period, most of them pefer to come 1, 2 and 3 days in a week and less prefer for 4 to 7 days a week  

Thanks,

Vishant

[toovishant]

@aj1973  - Its not the count by weekdays, but mention how many are coming 1 day, 2 days, 3 days, 4 days, 5 days, 6 days and 7 days in a week.

For Ex: if I select the data set for a period 1st July to  30th Nov 2023. I need to know for this selected period.

Hoe prefer to come 1 day, 2 days , 3 days 4 days and 5 days 

Regards,

Vishant

[gadielsolis]

Hello,

Here is the solution I propose:

1-Create a calculated column to extract the start of week for the date

Week =

    Sheet1[Date]-WEEKDAY(Sheet1[Date],2)+1

2-Create a calculated column to calculate the number of rows for that employee within that week (that should be the number of days that the employee came to the office): 

Days in the week =

        CALCULATE(COUNTROWS(Sheet1),

        FILTER(Sheet1,Sheet1[employee]=EARLIER(Sheet1[employee])&&Sheet1[Week]=EARLIER(Sheet1[Week])))

With those columns you should be able to represent this in a chart and shoud look like this:

If I answered your question, please mark this thread as accepted

[toovishant]

@gadielsolis  - Thank You for your response, please if possible can you share this pbix file.

Thanks,

Vishant

[gadielsolis]

Sure, here you have the link so you can donwload the file https://drive.google.com/file/d/161ocw7Q-Pn7DKX567ono3aRFIgHf6Tki/view?usp=sharing

If I answered your question, please mark this thread as accepted

[aj1973]

Hi @toovishant 

Is this what you want to see?