Forum Discussion
Calculating Month value based on Cumulative Weekly Data with 2 variables
Hello
I need to add a column to my data which returns the month movement for a sepcified filter. The data is in the screen shot below, for example the new column would have 30.47 for line 1 (Week 1 of the year). For line 2 the value would be 47.6, line 3 would be 49.75 and so on.
The new column needs to be dynamic in that it returns the value based on the Name of applicant and Time type & week - i have many employees and many time types in the data set.
For information the number of hours column is a cumulative number based on the employee/Date (Week Ending) and time type.
I am also not sure if its easier to cacluate using a measure or a calcualted column in the data ?
Any help would be greatly appreciated.
Thanks
UK06B1 , Based on what I got so far,
You can new column that give you weekly values
new column =
var _max = maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] <earlier([Week ending])), [week ending])
return
[Number of hours] - maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] =_max), [Number of hours])You can distribute this table with help date table if needed
new column =
var _max = maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] <earlier([Week ending])), [week ending])
return
[Number of hours] - maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] =_max), [Number of hours])How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
Hi UK06B1
Here is a method of calculated column.
Month Movement = VAR __lastWeekEnding = 'Table (2)'[Week Ending] - 7 RETURN 'Table (2)'[Number of Hours] - MAXX ( FILTER ( 'Table (2)', 'Table (2)'[Name] = EARLIER ( 'Table (2)'[Name] ) && 'Table (2)'[Time Type] = EARLIER ( 'Table (2)'[Time Type] ) && 'Table (2)'[Week Ending] = __lastWeekEnding ), 'Table (2)'[Number of Hours] )Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
4 Replies
Hi UK06B1
Here is a method of calculated column.
Month Movement = VAR __lastWeekEnding = 'Table (2)'[Week Ending] - 7 RETURN 'Table (2)'[Number of Hours] - MAXX ( FILTER ( 'Table (2)', 'Table (2)'[Name] = EARLIER ( 'Table (2)'[Name] ) && 'Table (2)'[Time Type] = EARLIER ( 'Table (2)'[Time Type] ) && 'Table (2)'[Week Ending] = __lastWeekEnding ), 'Table (2)'[Number of Hours] )Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Brilliant thanks - and apologies for delay in accepting the solution.
UK06B1 , Based on what I got so far,
You can new column that give you weekly values
new column =
var _max = maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] <earlier([Week ending])), [week ending])
return
[Number of hours] - maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] =_max), [Number of hours])You can distribute this table with help date table if needed
new column =
var _max = maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] <earlier([Week ending])), [week ending])
return
[Number of hours] - maxx(filter(Table, [Employee Name] = earlier([employee name]) && [Week ending] =_max), [Number of hours])How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
Hi
this seems to work for the first week but when i look at the table and bring in the new column it doesnt have the correct number for the 2nd week ? see below .
I included the formula which you gave in the screen shot - and I cant see that i have made an error in the formula ?
thanks again for your help.
