I have a time card app with 10,000+ records and 50 different users. Time is entered on a project basis not day basis, so each day, each person might have 8 entries. The time card app works well. What I want to do is count how many times a person has worked a weekend, and then later put this in a dynamic dashboard to filter out dates from and to.
I am counting a weekend worked as working on any Saturday or Sunday in a given week year where they have booked 8 or more hours.
I have built the formulas which gave me the correct answer but this only worked when I set up the slice to only look at my name (so I could test and create it easier). Now when I remove the restriction to only look at my name, I get the wrong answer as it is looking at everyone and adding up all the weekends. I understand why it is doing this, but I don't know how to solve it.
The data table contains 40+ rows but for simplicity the data table contains
- Username - Name of the person
- Project Name - The job name that have worked on
- Week Year - The year and week number i.e. 2023 - 8, 2023 - 9, 2023 - 10
- Total Hours - The total hours they have booked to that project name
- Name Day - Saturday, Sunday etc
My Slice (Weekend Working) looks like this, which removes not needed data and just looks at entries made on a Saturday or Sunday. (When testing it I had Username = my name in the AND section and everything worked fine).
AND(
([Project Name] <> "rubbish data"),
([Project Name] <> "rubbish data 2"),
([Project Name] <> "rubbish data 3"),
OR(
([Day Name] = "Saturday"),
([Day Name] = "Sunday")
))
Then I have these virtual columns
VC - Sum Weekend Working - This counts the total hours for that Week Year on a Saturday and Sunday and if it is greater than 7.9, returns a 1 or if not a 0
IF(SUM(select(Weekend Working[Total Hours], [Week Year]=[_ThisRow].[Week Year]))>7.9,1,0)
VC - Count Per Week - This looks at the above and counts how many entries there are
SUM(select(Weekend Working[VC - Sum Weekend Working], [week year]=[_ThisRow].[week year]))
VC - Weekends Combined - This looks at the above and if it is 1 or more it creates text with the Week Year number and the word Yes. If not, it says not worked
if([VC - Count Per Week] > 0.9, CONCATENATE([Week Year]," - Yes"), "Not Worked")
VC - Weekends Count - Finally, this counts how many unique entries are produced from the above and minuses 1 for the Not Worked entry, to give me a total of how many weekends have been worked
(COUNT(SELECT(Weekend Working[VC - Weekends Combined], TRUE, TRUE))) - 1
How can I get the above to work to count up how many weekends each user has worked (and then put that number against their name), rather than looking at the whole company? I know I am missing a step or two but I can't figure out where to start. Any pushes in the right direction would be appreciated.
Thanks,
LinkedIn
Twitter
