Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate II
Advocate II

Compare a subset of machines performance in a week and what it was 2 weeks before



I have been stuck on this for a couple of days now.


The scenario is like this, I have a table that is a "weekly performance" list for a population of machines and their performance with each machine having a unique ID. So the Table is Machine_ID, Week_Date, Performance.


I have also added an Index key that is the concatenation of Machine_ID & Week_Date


The second table  "serviced machines" lists what machines were serviced in a particular week. So the Table is Machine_ID, Week_Date and a concatenation to create an Index as above.


What I want to do is take all the Machine_ID's that were serviced in a week and calculate their average performance for that week AND ALSO what was there average performance 2 weeks earlier.


I have tried a number of ways but with without success -- any ideas how I could do this?



Super User
Super User

Need sample/example data in text form. Please see this post regarding How to Get Your Question Answered Quickly:


That being said...See my article on Mean Time Before Failure (MTBF) which uses EARLIER:

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,


Thanks for the link very useful and hopefully I have managed to take most on board with this reply.  I looked at your MTBF article it was interesting and I think maybe someway to what I want to do but i am specifically trying to use subgroups of machines and seeing the before and after some Activity was applied.


Problem Statement


I'll use a a simplified model to explain as the implementation is with a much larger model we run.  


Assume there is a population of 100 machines/objects and every week as well as the normal data we get on these we also get Field data on UpTime for a subset of the base. We perform work on a small percentage of the machines every week and the "Exam Question" I want to answer is for the small subset of machines were work was perform, and we have UpTime data, did their Average UpTime improve when we compare their average UpTime 2 weeks before the action and 2 weeks after and calculate this each week for the weeks unique small pool of machines.   


I have the following Tables in the Model


Used to group dates into weeks
Holds by week data relevant to how the machine was performing along with other data used to slice the model
Each week a small percentage of the machines have work done to them this table documents that activity
This table holds by week a subset of the machines in the History Table  where we get Uptime performance by machine.


The relationship currently flow top down in the order of the list above


Calendar History
History Activity
Activity FieldData


Answering the Exam Question


I have simplified the calculations I am looking for by looking at 1 week in this explanation but I need the solution to iterate through all the weeks.


In the week of 22/04/2018 there where 5 machines that had work done to them so if we filter this weeks ACTIVITY table for 22/01/2018 and is there a record in FieldData table we see -



Table - ACTIVITY    
WEEKIDWeekStart_IDOther field used in slicers
 22/04/2018ID123 22/04/2018_ID123 
 22/04/2018ID976 22/04/2018_ID976 
 22/04/2018ID532 22/04/2018_ID532 
 22/04/2018ID232 22/04/2018_ID232 
 22/04/2018ID345 22/04/2018_ID345 



and in the FieldData table (unfiltered just now) we would have many records -


Table - FeildData   
 22/04/2018ID123 22/04/2018_ID1230.98125



From this data I am trying to do the following


The calculation:Take the 5 ID's in week 22/04/2018 and create a table from FieldData that contains all the records associated with these 5 ID in the table
 Average Uptime (-2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, - 14,  DAYS)
 Average Uptime (+2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, + 14,  DAYS)
 Diff in Uptime = Average Uptime(+2 Weeks) - Average Uptime(-2 Weeks) 



Expected results


A table that covers the period we have data on


WeekAverage Uptime (-2 weeks)Average Uptime (+2 weeks)Diff in Uptime



Current Status


I tried a number of approaches and still stuck - it might be I am looking at this from the wrong prospective I know its possible. In writing up this I am question myself in do I have the structure correct maybe my FeildData needs to have a relationship with the History table and not the Activity Table


Any help/guidance would be gratefully appreciated.



Hi @Timaru_Golf,

How do you calculate the [Uptime]? In your sample table, how to get 0.98125 and 0.99658?

Best Regards,



In the FieldData table each machine has a row per week where the uptime for that week is stored.





Today I have been trying to break the problem into mini steps and build up to a solution..


So first how many units where touch in a week. The following measure works used in a Matrix table Week for the Rows works fine


Units Touched = COUNTROWS(VALUES(FieldData[ID])


Next I tried to create the same results but now opening the data up to the full table so I could walk back and forth in the data later but just now keep it this week.


TEST ALL Filtered this week & ID's = 

VAR Units_in_this_week = VALUES(FieldData[ID])


                   EARLIER(FieldData[Date]) = DATEADD(Calendar[WeekStart], -0, DAY)
                   && FieldData[ID] in Units_in_this_week

The Above Formula I adapded from a post by Alejandro Zuleta and link is


From what I can see it is basically ignorning the "Units in Field" condition and just returning all the records that week.


So what am I doing wrong with -- && FieldData[ID] in Units_in_this_week  -- or just my basic logic?





Hi @Timaru_Golf,

You need to list the specific value for Units_in_this_week part, please review the IN function.


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors