Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Showing week commencing over week commencing values per individual

Hi Everyone,

I wonder if you can help me. My data look like below. Two sets of data apended to 1 query (below)

 

Person ID - Name - Team - Week commencing - Hours

12345          John      Fin          02/02/2020               5

12345          John      Fin          26/01/2020               10

12346          Mike      Fin          02/02/2020              3

12346          Mike      Fin          26/02/2020               15

 

On the report I have a table visual that is filtered to most recent week commencing (02/02/2020) and I would like to show a new column with previous week hours.

 

so it will look like that:

Person ID - Name - Team - Week commencing - Hours - Last Week Hours

12345          John      Fin          02/02/2020               5                  10

12346          Mike      Fin          02/02/2020              3                   15

 

Any thoughts/ideas? I looked at this forum, watched youtube videos, tried many options but unsuccessfully 😞 

Need Help
  • Omega's avatar
    Omega
    6 years ago

    Hi, 

     

    Try the below measure: 

     

    Measure = CALCULATE(SUM('Table'[Hours]),ALLEXCEPT('Table','Table'[Person ID]),DATEADD('Table'[Week],-7,DAY))

2 Replies

  • Omega's avatar
    Omega
    Icon for Impactful Individual rankImpactful Individual
    6 years ago

    Hi, 

     

    Try the below measure: 

     

    Measure = CALCULATE(SUM('Table'[Hours]),ALLEXCEPT('Table','Table'[Person ID]),DATEADD('Table'[Week],-7,DAY))
  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please try to create a Measure like so:

    Last Week Hours = 
CALCULATE (
    SUM ( 'Table'[Hours] ),
    ALLSELECTED ( 'Table'[Person ID] ),
    DATEADD ( 'Table'[Week commencing], -7, DAY )
)

     

     

    Best Regards,

    Icey

     

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