Latest Enterprise DNA Initiatives

Employee Total Hours For 2 week timeframe

DAX Calculations
#1

Hello,

I’m looking for guidance on a DAX measure. Our employees are paid every 2 week. The data as it’s captured in the fact table is week 1 data is assigned week 1 and week 2 is assigned week 2. This week 1/week 2 is always repeated. Each week is also assigned a unique week ending date (Sunday of the week) AND a unique time period.

What I need to do, is sum the hours for Week 1 and Week 2 - based on the actual calendar so in this case Week 1 is Week Ending 12/6/20 and Week 2 is Week Ending 12/13/20 AND if the sum of those 2 weeks is over 80, I need to flag it.

exampleEmpHours

I need to be able to ensure this repeats through all of my data, ensuring the addition of week 1 and week 2 is following the logical calendar.

I suspect the Time Period column can assist with this calculation I’m just struggling with how to put this all together.

I’ve included a .pbix file with the sample data as well.

Thank you.

EmpHours.pbix (49.5 KB)

#2

Hi @mvgust,

You’ve reached the point where a Date dimension table is required because you are venturing into Time Intelligence calculations.

Below an excerpt of this Microsoft article

To work with Data Analysis Expressions (DAX) time intelligence functions, there’s a prerequisite model requirement: You must have at least one date table in your model. A date table is a table that meets the following requirements:

  • It must have a column of data type date (or date/time )—known as the date column .
  • The date column must contain unique values.
  • The date column must not contain BLANKs.
  • The date column must not have any missing dates.
  • The date column must span full years. A year isn’t necessarily a calendar year (January-December).
  • The date table must be marked as a date table .

Now I couldn’t provide a proper Date dimension table because I don’t know the specific requirements in your case. But I did create a small sample to illustrate it’s importance. This table should contain all date dimension attributes that are important for your specific business requirements and time intelligence.

image
image742×614 40.6 KB

As you can see I’ve added a Fortnight ID that groups the 1 and 2 Week Modulo together. With this in play the logic and DAX become way more easy.

image
image815×590 21.3 KB

And now you know the 2 week value you can easily test if that’s greater than 80 hours.

Here’s your sample file: EmpHours.pbix (79.5 KB)
I hope this is helpful

3 Likes
#3

Hi @mvgust, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#4

Thank you Melissa. This has been very helpful in clarifying this and another issue I’ve been having with another report. Looks like I’ve got some additional work to do to try to figure out how added week number and time period to my dates table (which I have for my actual model). Each of those columns only reside in the fact table at the company I work for.

1 Like
#5

Hi @mvgust,

Sounds like you can Merge them back to your Date table then based on the Week End date…
However you can always create a new topic if you need assistance with that, thanks.

All the best