Calculating weekly change of cumulative data

[UK06B1]

Hi 

I am trying to solve a problem i have. My data is on a cumulative basis (weekly data downloads). And I would like to calculate the weekly amounts. For example in the below table the worked hours is cumulating but i would like to return the value for each week. Therefore week 1 would be 22, week 2 = 58 hours (80-22) , week 3 = 54 (134-80) etc . I have spent a couple of hours on this now, which i thought would be a relatively straight forward solution. Any help would be greatly appreciated. 

NOTE: in my date table i have already created a week number, month number, year number column which i was using to try and solve using various measures, but failed. 

Thanks 

[ryan_mayu]

@UK06B1 

you can create a column

Column = 
VAR _date=MAXX(FILTER('Table','Table'[date]<EARLIER('Table'[date])&&year('Table'[date])=year(EARLIER('Table'[date]))),'Table'[date])
return 'Table'[worked hours]-maxx(FILTER('Table','Table'[date]=_date),'Table'[worked hours])

[UK06B1]

This looks like it would work - howere is there a way to make it variable, in between date and worked hours column I have employee names - in which case i would like the new column to work out the hours worked in the week + by person. 

[ryan_mayu]

@UK06B1 

maybe you can try

Column =
VAR _date=MAXX(FILTER('Table','Table'[date]<EARLIER('Table'[date])&&employee=earlier(employee)&&year('Table'[date])=year(EARLIER('Table'[date]))),'Table'[date])
return 'Table'[worked hours]-maxx(FILTER('Table','Table'[date]=_date),'Table'[worked hours])

If you can provide the sample data and expected output, that will be better for us to provide the best solution

[UK06B1]

I can seem to attach any data to the thread - would a screen shot of some sample data, the data table and the expected outcome work ? thanks 

[ryan_mayu]

@UK06B1 

you can try that. Let's see if this will work.

[UK06B1]

apologies for the delay - here is the snapshot of essentially the 2 tables of data (Employee - with cumulative hours worked to the week ending) | date table which i am using in the model | 3rd table essentially with the expected out come (Worked hours in the week - yellow column).

In the end I did use power query to get the solution i needed but that’s because of time - and resorting to alot of manual calc files - I would prefer to understand if/how this is possible as it will definitely be useful for the future.

Thanks for your help.

[ryan_mayu · Accepted solution]

@UK06B1 

please  see the attachment below

[UK06B1]

Brilliant that works  - thanks alot for your help. 

[ryan_mayu]

not be sure why you need the 3rd table. I think you can just create a column in employee table.

[UK06B1]

yeah thats fine - it was just for presentation purposes - idea would be to get the highlighted col in the emplyee table -either through calculated column or some other means . thanks 

[Anonymous]

I don't understand what prevents you from calulating the weekly numbers in Power Query... or in the source.

[UK06B1]

the source data is 17 downloads (one for each week so far) with many columns and many different variables - in essence i could just use the source excel files and manually calculate but then i would need to do this for every variable (person) and ever week - plus there are new starters and people leaving so becomes very complictated when doing it in Excel (too large to cope with all the vlookups). I was hoping this would be as simpe as current week - previous week value for the set of filters defined (Person | Week/Date). ? which i could then use in the model to work out the week on week changes for all the other variables. 

[Anonymous]

@UK06B1 

As much as I understand, the separate files you get have the same structure. This makes it dead easy to import all of them into Power Query and make the calculations you're after. On top of that, if you do it the right way - which means in Power Query - you'll reduce complexity and your DAX will be not only simpler, but also more speedy.

I have a feeling you're trying to sqeeze a square into a round hole. Please do not. And do not re-invent the wheel. Please get familiar with Power Query and this will set you free 🙂

[UK06B1]

hi,  i am now in the same psiton in power query with virtually the same model which appears to be  a mirror of power BI. I still have the same issue where i have a table of data with thousands of rows for individual weeks per employee - with the cuulative hours for that week. How can now get this into a weekly view ? i have treid power pivot using the model - but still cannot get the weekly data - how would you suggest getting the weekly number - i presume a caclculated column? 

NOTE : the date column has dates running up to current date. With the week end being the sundays. 

[UK06B1]

thanks , i really dont mind which solution to be honest - i will give power query a try