Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sum of current week

I have a payroll table that has a column containing amount of hours each employee worked for each day. 

 

I want to make a measure that calculates the current weeks total hours. 

 

This is what I currently have.

The calculation is based on the current date from a date slider.

WTD Hours = 
calculate(
    sum('Payroll'[Hours]),
    FILTER(
        All('Payroll'[Transaction Date]), WEEKNUM('Payroll'[Transaction Date]) = WEEKNUM(max('Date'[Date]),2)
    )
)

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your description, I think you want to calculate the sum of the hours of the current week grouped by employees. 

    You can use the TODAY function to get the week number of the current week.

    WTD Hours =
CALCULATE (
    SUM ( Payroll[Hours] ),
    FILTER (
        ALLSELECTED ( Payroll ),
        [Employe] = MAX ( 'Payroll'[Employe] )
            && WEEKNUM ( [Transaction Date], 2 ) = WEEKNUM ( TODAY (), 2 )
    )
)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

  • jeroendekk's avatar
    jeroendekk
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Anonymous 

    I think you need to place the max weeknum function outside of the calculate in a variable to get the correct result.

    Something like this:

    WTD Hours = 
VAR Selectedweeknr = WEEKNUM(max('Date'[Date]),2)

RETURN
calculate(
    sum('Payroll'[Hours]),
    FILTER(
        All('Payroll'[Transaction Date]), 
        WEEKNUM('Payroll'[Transaction Date]) = Selectedweeknr
    )
)



    If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

    Best regards,
    Jeroen

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to your description, I think you want to calculate the sum of the hours of the current week grouped by employees. 

    You can use the TODAY function to get the week number of the current week.

    WTD Hours =
CALCULATE (
    SUM ( Payroll[Hours] ),
    FILTER (
        ALLSELECTED ( Payroll ),
        [Employe] = MAX ( 'Payroll'[Employe] )
            && WEEKNUM ( [Transaction Date], 2 ) = WEEKNUM ( TODAY (), 2 )
    )
)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.