Count number of weeks from daily data

[Anonymous]

I have what is probably a very simple problem that I have been looking at too long and hope one of you can help me solve.

I have a payroll table with hours by employee by day. I am simply trying to write a measure that shows how many weeks they have worked.

4 tables (calendar, employee, location, and payroll) 

payroll table structure

I have been able to build many calaculations from these, but this one is the simplest thing I cannot get to work and have tried multiple methods.

[Anonymous · Accepted solution]

Sorry for the blurry images
I got the problem solved by changing the relationship between calendar and payroll to bi-directional

Now just trying to figure out how to speed up the DAX query becuase its slow when evaluating all employees (over 10K)

[Anonymous]

Hi @Anonymous ,

You could get the start date and end date by using min() and max() function for each user.

start_ = calculate(MIN('payroll'[date]),ALLEXCEPT('payroll','payroll'[user]))

end_ = calculate(max('payroll'[date]),ALLEXCEPT('payroll','payroll'[user]))

Then you could calculate the weeks between start date and end date by using datediff() function.

weeks_ = datediff([start_],[end_ ],week)

Best Regards,

Jay

[Anonymous]

Thanks for the reply, your solution would only work if the people worked in continuous weeks without any weeks off.

[MFelix]

Hi @Anonymous

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix]

Hi @Anonymous ,

Altough your explanation is complete has you can imagine redoing your model based on images only is very complicated can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.